[Power BI Query Tips] 取り込んだ Excel を加工する

  • Article

Microsoft Japan Business Intelligence Tech Sales Team 伊藤

前回は、同じフォーマットのシートを複数持つ Excel ブックを取り込む際の Tips をご紹介しましたが、今回はその続きで、不要な行や列を取り除き、分析しやすい形に整形する方法をご紹介します。

前回の手順で出来上がったファイルが こちら です。このファイルを開いて、[ホーム] → [クエリを編集] からクエリの編集を続けていきます。

 

image

以下、クエ リエディターでの操作手順です。ポイントなるところを赤字で表示します。

  1. 不要な行を削除します。
    1 月のデータが格納されている「Column2」に注目すると、ここが「null」である行は、データとして不要であることが分かります。
    image「Column2」のプルダウン メニューで「null」のチェックを外し [OK] をクリックします。

  2. 1 行目のデータを列名に設定するため、[ホーム] → [1  行目をヘッダーとして使用] をクリックします。
    image

  3. 列名を修正します。
    「2003」列、「Column2」列の列名をダブルクリックし、それぞれ「年」と「国」に変更します。
    列名を右クリックして [名前の変更] をクリックしても構いません。
    image

  4. 各月の「伸率」は後で計算可能なので、「2003」「国」、「1月」～「12月」の各列を選択し、右クリック メニューから [他の列の削除] を選択します。
    image
    必要な列だけを選択して「他の列を削除」することで、他のユーザーが勝手に追加した列や、データが入っていないはずなのにデータ領域として読み込まれてしまった列などがいくつあっても、柔軟に対応できます。  

  5. 月ごとに列がありますが、「年」と同様に「月」列に人数を格納するように変換します。ここでは以前の記事「Power Query で Excel マクロから卒業？！　- 列のピボット と ピボット解除 –」で紹介した「列のピボット解除」を使います。
    「年」「国」の 2 列を選択し、右クリックメニューから [その他の列のピボット解除] をクリックします。
    （今回は選択する列が少ない「列のピボット解除」を選択しました。）
    image 

    「属性」列に「1月」～「12月」という列名が格納され、対応する値が「値」列に格納されるので、それぞれ「月」と「人数」という列名に変更します。
    image

  6. 「総計」や「アジア計」などの不要な列を削除して、「国」列が列名の通り国だけになるようにします。
    値に「計」という文字を含む行を削除したいので、「国」列のプルダウン メニューから [テキスト フィルター] → [指定の値を含まない] を選択し、
    image下図のように入力して [OK] をクリックします。
    image

  7. 「月」列の値から「月」という文字を取り除きます。
    「月」列を右クリックし、[値の置換] を選択します。
    image[検索する値] に「月」と入力し、[OK] をクリックします。
    image

  8. 「月」列と「人数」列のデータ型を数値 (整数) にします。
    この 2 つの列を選択し、右クリックメニューから [型の変更] → [整数] をクリックします。
    image

  9. [ホーム] → [閉じて適用] をクリックして、クエリ エディターを終了します。
    image

 

「月」列と「年」列の値は集計しないものですので、設定を変更します。
「月」列をクリックし、[モデリング] → [既定の概要: 合計] → [集計しない] を選択します。
「年」列も同様に集計しないよう設定します。最初に設定したい列をクリックするのがポイントです。 image

以上の手順で、下図のようなレポートを作る準備ができました。
image

今回のできあがりファイルは こちら です。
地域を無視してデータを加工したので、次回は国を地域ごとにグルーピングする手順をご紹介します。