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

[アーティクル]
09/20/2024

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

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

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

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

データの整形

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

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

Power Query エディターの [クエリの設定] ペインと [適用したステップ] リストのスクリーンショット。

Web ソースからデータをインポートします。 [データの取得] ドロップダウンを選択し、[Web] を選択します。
この URL を [Web から] ダイアログに貼り付け、[OK] を選択します。
```
https://www.fool.com/research/best-states-to-retire
```
[ナビゲーター] ダイアログで、Individual factor scores で始まるエントリのチェックボックスをオンにし、[データの変換] を選択します。

ヒント

前の URL のテーブル内の一部の情報は、変更されたり更新されたりすることがあります。そのため、それに応じて、この記事の選択または手順の調整が必要になる場合があります。
[Power Query エディター] ウィンドウが開きます。これまでに適用された既定のステップは、[クエリの設定] ペインの [適用したステップ] で確認できます。
- [ソース] : Web サイトへの接続。
- [HTML から抽出されたテーブル] : テーブルを選択。
- 昇格されたヘッダー数: データの先頭行を列ヘッダーに変更します。
- 変更された型: テキストとしてインポートされる列の型を、推論型に変更します。
テーブル名を既定の Individual factor scores... から Retirement Data に変更し、Enter キーを押します。
既存のデータは、ソースの Web ページの手法のセクションで説明されているように、重み付けされたスコアで並べられています。次に、この列のテーブルを並べ替えて、カスタムスコアのランクを既存のスコアと比較します。
[列の追加] リボンで、[カスタム列] を選択します。
[カスタム列] ダイアログボックスで、[新しい列名] フィールドに「新しいスコア」と入力します。 [カスタム列の式] に、次のデータを入力します。
```
( [Quality of life] + [Cost of housing] + [Public health] + [Crime] + [Taxes] + [Weather] + [#"Non-housing cost of living"] ) / 7
```
ステータスメッセージが "構文エラーが検出されませんでした" であることを確認し、[OK] を選択します。
[クエリの設定] の [適用したステップ] リストに、先ほど定義した新しい [追加されたカスタム] ステップが表示されるようになりました。

データを調整する

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

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

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

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

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

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

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

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

注意

New score の値が変わっていません。これはステップの順序が原因です。 Power Query エディターによって、ステップが順番に記録されますが、互いに独立しています。アクションを異なる順序で適用するには、適用したステップをそれぞれ上下に移動できます。
ステップを右クリックすると、そのコンテキストメニューが表示されます。
コンテキストメニューから [上へ移動] を選択して、最後のステップ [削除された列] を [追加されたカスタム] ステップのすぐ上に移動します。マウスを使用して、ステップを目的の位置に移動することもできます。
[追加されたカスタム] ステップを選択します。

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

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

"エラー" という単語を選択すると、Power Query エディターによって、 [クエリの設定] ウィンドウに適用したステップが作成され、エラーに関する情報が表示されます。他の場所でエラー情報を表示する必要はないので、[キャンセル] を選択します。
エラーを修正するには、[Weather] 列の名前を削除し、除数を 7 から 6 に変更する 2 つの変更が必要です。これらの変更は、次の 2 つの方法で行うことができます。
1. [追加カスタム] ステップを右クリックし、[編集設定] を選択するか、ステップの名前の横にある歯車アイコンをクリックして、[新しいスコア] 列の作成に使用した [カスタム列] ダイアログを表示します。数式を前述したように編集して、次のようにします。
2. New score 列を選択し、[表示] タブで [数式バー] チェックボックスをオンにして、列のデータ数式を表示します。
  
  数式を前述したように編集して、次のようにしてから、Enter キーを押します。
```
= Table.AddColumn(#"Removed Columns", "New score", each ( [Quality of life] + [Cost of housing] + [Public health] + [Crime] + [Taxes] + [#"Non-housing cost of living"] ) / 6)
```
Power Query エディターによってデータが変更された値に置き換えられ、 [追加されたカスタム] ステップのエラーがなくなります。

注意

リボンまたは右クリックメニューを使用して [エラーの削除] を選択し、エラーのある行を削除することもできます。しかしながら、このチュートリアルでは、テーブルのすべてのデータを保持する必要があります。
New score 列を基準にして、データを並べ替えます。まず、最後に適用したステップの [追加されたカスタム] を選択して、最新のデータを表示します。次に、[新しいスコア] 列見出しの横にあるドロップダウンを選択し、[降順で並べ替え] を選択します。

データが、New score に従って並べ替えられます。適用したステップは、リストのどの位置のステップでも選択でき、順番のその時点からデータの整形を続行できます。新しいステップは、現在選択されている [適用したステップ] の直後に Power Query エディターによって自動的に挿入されます。
[適用したステップ] で、カスタム列の前のステップである [削除された列] ステップを選択します。ここで、オレゴン州の住宅費ランキングの値を置き換えます。オレゴン州の 住宅費の値が含まれる該当セルを右クリックし、[値の置換] を選択します。現在選択されている適用したステップを確認します。
[挿入] を選択します。

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

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

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

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

データの結合

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

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

Power Query エディターの [ホーム] リボンで、 [新しいソース] > [Web] を選択します。
州の略称の Web サイトのアドレス https://en.wikipedia.org/wiki/List_of_U.S._state_and_territory_abbreviations を入力し、[はい] を選択します。

[ナビゲーター] に、Web サイトのコンテンツが表示されます。
Codes and abbreviations for U.S. states, federal district, territories, and other regions を選択します。

ヒント

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

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

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

[上位の行の削除] ダイアログが表示されます。削除する 1 行を指定します。
Retirement Data テーブルにはワシントン DC と準州の情報がないため、リストからフィルター処理する必要があります。 [リージョン状態] 列のドロップダウンを選択し、[州] と [州 (正式には連邦)] を除くすべてのチェックボックスをオフにします。
不要な列をすべて削除します。必要なのは、各州と正式な 2 文字の省略形のマッピング (名前および ANSI 列) だけなので、他の列は削除できます。最初に [名前] 列を選択し、[Ctrl] キーを押しながら [ANSI] 列を選択します。リボンの [ホーム] タブで、[列の削除] >[他の列の削除] を選択します。

注意

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

注意

Power Query エディターウィンドウのサイズを変更して幅を狭くすると、表示スペースを最大限利用するようにリボン項目の一部が凝縮されます。 Power Query エディターウィンドウの幅を広げると、広くなったリボン領域を最大限活用するようにリボン項目が拡大されます。
列とテーブルの名前を変更します。列の名前を変更するにはいくつかの方法があります。まず列を選択して、リボンの [変換] タブで [名前の変更] を選択するか、右クリックして [名前の変更] を選択します。次の画像には両方のオプションが表示されていますが、選択する必要があるのは一方だけです。
列の名前を、State Name および State Code に変更します。テーブルの名前を変更するには、[クエリの設定] ウィンドウの [名前] に「州コード」を入力します。

クエリを結合する

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

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

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

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

Power Query エディターの左側のウィンドウで、他のクエリを "その中に" マージするクエリを選択します。この例では Retirement Data です。
リボンの [ホーム] タブから [クエリのマージ] > [クエリのマージ] を選択します。

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

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

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

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

[展開] ウィンドウが表示されます。
この例では、State Code 列のみが必要です。その列を選択し、[元の列名をプレフィックスとして使用します] をオフにして、[OK] を選択します。

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

Note

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

2 つのデータソースを結合した 1 つのクエリ (テーブル) ができ、それぞれがニーズに合わせて整形されました。このクエリは、あらゆる州の住宅費統計、生活の質、犯罪率など、興味深いデータ接続の基礎にすることができます。
変更を適用し、Power Query エディターを閉じるには、[ホーム] リボンタブから [閉じて適用] を選択します。

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