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

Microsoft Japan Business Intelligence Tech Sales Team 伊藤

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

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

 

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

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

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

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

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

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

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

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

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

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

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

 

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

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

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