チュートリアル: Power BI Desktop でデータの整形と結合

Power BI Desktop を使用すると、さまざまな種類のデータ ソースに接続し、ニーズに合わせてデータを整形し、他のユーザーと共有できるビジュアル レポートを作成できます。 データの "整形" とは、データを変換することです。たとえば、列やテーブルの名前を変更したり、テキストを数値に変更したり、行を削除したり、最初の行をヘッダーとして設定したりします。 データの "結合" とは、複数のデータ ソースに接続して、必要に応じてそれらを整形してから、単一のクエリに統合することを意味します。

このチュートリアルで学習する内容は次のとおりです。

  • Power Query エディターを使用してデータを整形します。
  • さまざまなデータ ソースに接続します。
  • これらのデータ ソースを結合し、レポートで使用するデータ モデルを作成します。

このチュートリアルでは、最も一般的なタスクに注目しながら、Power BI Desktop を使用したクエリの整形方法を示します。 ここで使用するクエリは、クエリを最初から作成する方法も含めて、「Power BI Desktop の概要」でより詳しく説明しています。

Power BI Desktop の Power Query エディターでは、右クリック メニューと [変換] リボンを使用します。 そのリボンで選択できるほとんどの項目は、項目 (列など) を右クリックし、表示されるメニューからクリックして使用することもできます。

データの整形

Power Query エディターでデータを整形するため、Power Query エディターでデータを読み込み、表示する際にデータを調整するステップ バイ ステップの手順を提供します。 元のデータ ソースが影響を受けることはありません。この特定のデータ表示のみが調整つまり "整形" されます。

指定したステップ (テーブル名の変更、データ型の変換、列の削除など) は、Power Query エディターによって記録されます。 このクエリがデータ ソースに接続されるたびに、Power Query エディターによってこれらのステップが実行されることで、データが常に指定したとおりに整形されます。 自分が Power Query エディターを使用するたび、または別のユーザーが Power BI サービスなどで共有クエリを使用するときに、このプロセスが実行されます。 これらのステップは、[クエリの設定] ペインの [適用したステップ] で順番にキャプチャされます。 この記事では、これらの各ステップについて説明します。

Screenshot of Power Query Editor with the Query Settings pane and Applied steps list.

  1. Web ソースからデータをインポートします。 [データの取得] ドロップダウンを選択し、[Web] を選択します。

    Screenshot of Power Query Editor with the Get data menu and Web source selected.

  2. この URL を [Web から] ダイアログに貼り付け、[OK] を選択します。

    https://www.fool.com/research/best-states-to-retire
    

    Screenshot of Power Query Editor's From Web dialog with the source page's URL entered.

  3. [ナビゲーター] ダイアログで、Table 1 を選択し、[データの変換] を選択します。

    Screenshot of Power Query Editor's Navigator dialog with HTML Table 1 selected and the Transform Data button highlighted.

ヒント

前の URL のテーブル内の一部の情報は、変更されたり更新されたりすることがあります。 そのため、それに応じて、この記事の選択または手順の調整が必要になる場合があります。

  1. [Power Query エディター] ウィンドウが開きます。 これまでに適用された既定のステップは、[クエリの設定] ペインの [適用したステップ] で確認できます。

    • [ソース] : Web サイトへの接続。
    • [HTML から抽出されたテーブル] : テーブルを選択。
    • 昇格されたヘッダー数: データの先頭行を列ヘッダーに変更します。
    • 変更された型: テキストとしてインポートされる列の型を、推論型に変更します。

    Screenshot of the Power Query Editor window with Query Settings highlighted.

  2. テーブル名を既定の Table 1 から Retirement Data に変更し、Enter キーを押します。

    Screenshot of Power Query Editor showing how to edit a table name in Query Settings.

  3. 既存のデータは、ソースの Web ページの手法のセクションで説明されているように、重み付けされたスコアで並べられています。 別のスコアを計算するカスタム列を追加しましょう。 次に、この列のテーブルを並べ替えて、カスタム スコアのランクを既存の Rank と比較します。

  4. [列の追加] リボンで、[カスタム列] を選択します。

    Screenshot of Power Query Editor's Add Column ribbon with the Custom Column button highlighted.

  5. [カスタム列] ダイアログ ボックスで、[新しい列名] に「New score」と入力します。 [カスタム列の式] に、次のデータを入力します。

    ( [Quality of life] + [Housing cost] + [Healthcare cost and quality] + [Crime rate rate] + [#"Public health/COVID-19 response"] + [Sales taxes] + [#"Non-housing costs"] + [Weather] ) / 8
    
  6. ステータス メッセージが "構文エラーが検出されませんでした" であることを確認し、[OK] を選択します。

    Screenshot of Power Query Editor's Custom Column dialog showing the new column name, custom column formula, and no syntax errors.

  7. [クエリの設定][適用したステップ] リストに、先ほど定義した新しい [追加されたカスタム] ステップが表示されるようになりました。

    Screenshot of Power Query Editor's Query Settings pane showing the Applied Steps list with the actions so far.

データを調整する

このクエリを処理する前に、いくつか変更を加えてデータを調整しましょう。

  • 列を削除してランキングを調整します。

    たとえば、Weather が結果の要因ではないとします。 クエリからこの列を削除しても、他のデータに影響しません。

  • エラーがあれば修正します。

    列を削除したので、New score 列の計算を、式を変更して再調整する必要があります。

  • データを並べ替えます。

    New score 列に基づいてデータを並べ替え、既存の Rank 列と比較します。

  • データを置き換えます。

    特定の値を置き換える方法と、適用したステップを挿入する方法について説明します。

これらの変更について、次の手順で説明します。

  1. Weather 列を削除するには、列を選択し、リボンの [ホーム] タブを選択してから、[列の削除] を選択します。

    Screenshot of Power Query Editor's Home menu with the Remove Columns button highlighted.

    Note

    New score の値が変わっていません。これはステップの順序が原因です。 Power Query エディターによって、ステップが順番に記録されますが、互いに独立しています。 アクションを異なる順序で適用するには、適用したステップをそれぞれ上下に移動できます。

  2. ステップを右クリックすると、そのコンテキスト メニューが表示されます。

    Screenshot of Power Query Editor's Applied Steps context menu.

  3. 最後のステップ [削除された列][追加されたカスタム] ステップのすぐ上に移動します。

    Screenshot of Power Query Editor's Applied Steps list with the Removed Columns step now moved above the Custom Column step.

  4. [追加されたカスタム] ステップを選択します。

    New score 列に、計算値ではなく、[エラー] が表示されるようになりました。

    Screenshot of Power Query Editor and the New score column containing Error values.

    各エラーについてより詳しい情報を得るには、いくつかの方法があります。 "エラー" という語をクリックせずにセルを選択すると、Power Query エディターにエラー情報が表示されます。

    Screenshot of Power Query Editor showing the New score column with Error details.

    "エラー" という単語を選択すると、Power Query エディターによって、 [クエリの設定] ウィンドウに適用したステップが作成され、エラーに関する情報が表示されます。 他の場所でエラー情報を表示する必要はないので、[キャンセル] を選択します。

  5. エラーを修正するには、Weather 列の名前を削除し、除数を 8 から 7 に変更する 2 つの変更が必要です。 これらの変更は、次の 2 つの方法で行うことができます。

    1. [カスタム列] ステップを右クリックし、[設定の編集] を選択します。 New score 列の作成に使用した [カスタム列] ダイアログが表示されます。 数式を前述したように編集して、次のようにします。

      Screenshot of Power Query Editor's Custom Column dialog with formula errors fixed.

    2. New score 列を選択し、[表示] タブで [数式バー] チェックボックスをオンにして、列のデータ数式を表示します。

      Screenshot of Power Query Editor showing the New score column and its data formula with errors fixed.

      数式を前述したように編集して、次のようにしてから、Enter キーを押します。

      = Table.AddColumn(#"Removed Columns", "New score", each ( [Quality of life] + [Housing cost] + [Healthcare cost and quality] + [Crime rate rate] + [#"Public health/COVID-19 response"] + [Sales taxes] + [#"Non-housing costs"] ) / 7)              
      

    Power Query エディターによってデータが変更された値に置き換えられ、 [追加されたカスタム] ステップのエラーがなくなります。

    注意

    リボンまたは右クリック メニューを使用して [エラーの削除] を選択し、エラーのある行を削除することもできます。 しかしながら、このチュートリアルでは、テーブルのすべてのデータを保持する必要があります。

  6. New score 列を基準にして、データを並べ替えます。 まず、最後に適用したステップの [追加されたカスタム] を選択して、最新のデータを表示します。 次に、New score 列見出しの横にあるドロップダウンを選択し、[降順で並べ替え] を選択します。

    Screenshot of Power Query Editor showing the New score column with Sort Descending highlighted.

    データが、New score に従って並べ替えられます。 適用したステップは、リストのどの位置のステップでも選択でき、順番のその時点からデータの整形を続行できます。 新しいステップは、現在選択されている [適用したステップ] の直後に Power Query エディターによって自動的に挿入されます。

  7. [適用したステップ] で、カスタム列の前のステップである [削除された列] ステップを選択します。 ここで、オレゴン州の Housing cost ランキングの値を置き換えます。 オレゴン州の Housing cost 値が含まれる該当セルを右クリックし、[値の置換] を選択します。 現在選択されている適用したステップを確認します。

    Screenshot of the Power Query Editor window showing the Housing cost column with the Replace Values right-click menu item highlighted.

  8. [挿入] を選択します。

    ステップを挿入しようとしているため、Power Query エディターから、以降のステップでクエリが中断される可能性があることが通知されます。

    Screenshot of Power Query Editor's Insert Step verification dialog.

  9. データ値を 100.0 に変更します。

    Power Query エディターにより、オレゴン州のデータが置き換えられます。 適用したステップを新規作成すると、Power Query エディターにより、操作に基づいた名前 (この場合は [置き換えられた値]) が付けられます。 クエリ内に同じ名前のステップが 2 つ以上存在する場合、Power Query エディターによって、後続の適用するステップの名前に数字が追加され、この数字が 1 つずつ増えていきます。

  10. 最後の適用したステップである [並べ替えられた行] を選択します。

    オレゴン州の新しいランキングに関するデータが変更されたことに注目します。 このように変更されるのは、[置き換えられた値] ステップを適切な位置 ([追加されたカスタム] ステップの前) に挿入したためです。

    これで、データを必要な範囲で整形できました。 次に、別のデータ ソースに接続し、データを結合しましょう。

データの結合

さまざまな州に関するデータは興味深く、さらなる分析作業とクエリの構築に役立ちます。 しかしながら、州に関するほとんどのデータには、州の正式名ではなく、州コードの 2 文字の省略形が使用されています。 何らかの方法により、州名をその省略形に関連付ける必要があります。

その関連性を提供する別の公的データ ソースがありますが、退職者テーブルに接続する前に、整形がかなり必要です。 データを整形するには、次の手順に従います。

  1. Power Query エディターの [ホーム] リボンで、 [新しいソース] > [Web] を選択します。

  2. 州の略称の Web サイトのアドレス https://en.wikipedia.org/wiki/List_of_U.S._state_abbreviations を入力し、 [接続] を選択します。

    [ナビゲーター] に、Web サイトのコンテンツが表示されます。

    Screenshot of Power Query Editor's Navigator page showing the Codes and abbreviations table selected.

  3. Codes and abbreviations for U.S. states, federal district, territories, and other regions を選択します。

    ヒント

    このテーブルのデータを減らして必要なものだけにするには、少し整形する必要があります。 次の手順をより迅速にまたはより簡単に実行する方法はあるでしょうか。 はい。2 つのテーブル間に リレーションシップ を作成し、そのリレーションシップに基づいてデータを整形するという方法があります。 次の手順例は、テーブルを操作する場合に役立ちます。 ただし、リレーションシップは、複数のテーブルのデータをすばやく使用するのに役立ちます。

データ整形するには、次の手順のようにします。

  1. 先頭の行を削除します。 それは Web ページのテーブルを作成した方法のために作成されたものであり、不要です。 [ホーム] リボンで、 [行の削除] > [上位の行の削除] を選択します。

    Screenshot of Power Query Editor highlighting the Remove Rows dropdown and the Remove Top Rows item.

    [上位の行の削除] ダイアログが表示されます。 削除する 1 行を指定します。

  2. [ホーム] タブまたはリボンの [変換] タブで [先頭の行を見出しとして使用] を使用して、新しい先頭行を見出しに昇格します。

  3. Retirement Data テーブルにはワシントン DC と準州の情報がないため、リストからフィルター処理する必要があります。 Name and status of region_1 列のドロップダウンを選択し、[State] を除くすべてのチェックボックスをオフにします。

    Screenshot of Power Query Editor showing a column filter with only the State value selected.

  4. 不要な列をすべて削除します。 必要なのは、各州と正式な 2 文字の省略形のマッピング (Name and status of region および ANSI 列) だけなので、他の列は削除できます。 最初に Name and status of region 列を選択し、Ctrl キーを押しながら ANSI 列を選択します。 リボンの [ホーム] タブで、[列の削除] >[他の列の削除] を選択します。

    Screenshot of Power Query Editor highlighting the Remove Columns dropdown and the Remove Other Columns item.

    Note

    Power Query エディターの適用したステップの "順番" が重要であり、データの整形方法に影響します。 また、1 つのステップが別の後続のステップに与える影響を考慮することも重要です。 たとえば、適用したステップからステップを削除した場合、後続のステップが当初の意図したとおりに動作しない可能性があります。

    注意

    Power Query エディター ウィンドウのサイズを変更して幅を狭くすると、表示スペースを最大限利用するようにリボン項目の一部が凝縮されます。 Power Query エディター ウィンドウの幅を広げると、広くなったリボン領域を最大限活用するようにリボン項目が拡大されます。

  5. 列とテーブルの名前を変更します。 列の名前を変更するにはいくつかの方法があります。まず列を選択して、リボンの [変換] タブで [名前の変更] を選択するか、右クリックして [名前の変更] を選択します。 次の画像には両方のオプションが表示されていますが、選択する必要があるのは一方だけです。

    Screenshot of Power Query Editor highlighting the Rename button and also the Rename right-click item.

  6. 列の名前を、State Name および State Code に変更します。 テーブルの名前を変更するには、[クエリの設定] ペインの [名前]に「State Codes」と入力します。

    Screenshot of Power Query Editor window showing the results of shaping state codes source data into a table.

クエリを結合する

State Codes テーブルを希望どおりに整形したので、これら 2 つのテーブル (つまりクエリ) を 1 つに結合しましょう。 現在のテーブルはデータに適用したクエリの結果であるため、多くの場合、"クエリ" と呼ばれます。

クエリの結合には、"マージ" と "追加" という主な 2 つの方法があります。

  • 別のクエリに追加する "列" が 1 つ以上ある場合は、クエリを "マージ" します。
  • 既存のクエリに追加する 1 つ以上のデータ "行" について、クエリを "追加" します。

ここではクエリをマージします。

  1. Power Query エディターの左側のウィンドウで、他のクエリを "その中に" マージするクエリを選択します。 この例では Retirement Data です。

  2. リボンの [ホーム] タブから [クエリのマージ] > [クエリのマージ] を選択します。

    Screenshot of Power Query Editor's Merge Queries dropdown with the Merge Queries item highlighted.

    転送対象外のデータを含めたり転送したりせずに確実にデータが結合されるように、プライバシー レベルを設定するように求められる場合があります。

    [マージ] ウィンドウが表示されます。 選択したテーブルにマージするテーブルの選択と、マージに使用する一致する列の選択を求めるメッセージが表示されます。

  3. Retirement Data テーブルから State を選択し、State Codes クエリを選択します。

    一致する列を選択すると、[OK] ボタンが有効になります。

    Screenshot of Power Query Editor's Merge dialog.

  4. [OK] を選択します。

    Power Query エディターで、クエリの最後に新しい列が作成されます。この列には、既存のクエリとマージされたテーブル (クエリ) の内容が格納されます。 マージされたクエリのすべての列がこの列に凝縮されていますが、テーブルを展開して、必要な列をどれでも含めることができます。

  5. マージされたテーブルを展開し、含める列を選択するには、展開アイコン () を選択します。

    [展開] ウィンドウが表示されます。

    Screenshot of Power Query Editor's column Expand dialog showing the State Code column highlighted.

  6. この例では、State Code 列のみが必要です。 その列を選択し、[元の列名をプレフィックスとして使用します] をオフにして、[OK] を選択します。

    [元の列名をプレフィックスとして使用します] チェックボックスをオンのままにした場合は、マージされた列の名前が State Codes.State Code になります。

    Note

    State Codes テーブルを取り込む方法を調べる場合、少し試すことができます。 結果に満足できない場合は、[クエリの設定] ペインの [適用したステップ] リストからそのステップを削除すれば、クエリは展開ステップを適用する前の状態に戻ります。 展開プロセスが希望どおりになるまで、何回でも好きなだけ実行できます。

    2 つのデータ ソースを結合した 1 つのクエリ (テーブル) ができ、それぞれがニーズに合わせて整形されました。 このクエリは、あらゆる州の住宅費統計、生活の質、犯罪率など、興味深いデータ接続の基礎にすることができます。

  7. 変更を適用し、Power Query エディターを閉じるには、[ホーム] リボン タブから [閉じて適用] を選択します。

    変換されたセマンティック モデルが Power BI Desktop に表示され、レポートの作成に利用できます。

    Screenshot of Power Query Editor's Close & Apply button.

Power BI Desktop とその機能について詳しくは、次のリソースを参照してください。