[Power BI Query Tips] Excel の複数シートを効率的に取り込む方法
Microsoft Japan Business Intelligence Tech Sales Team 伊藤
Power BI Desktop はクエリ エディターで [クエリの追加] を使うことで、同じフォーマットの複数のデータ (クエリ) を結合することができます。データベース用語でいうところの UNION ALL や APPEND の処理です。
1 つの Excel ファイルから取り込む場合、シートごとに同じようなクエリを作って [クエリの追加] を行うのではなく、もっとスマートに結合できます。今回はその方法を日本政府観光局 (JNTO) のファイル https://www.jnto.go.jp/jpn/statistics/since2003_tourists.xlsx を使って説明します。 
こちらは、年別のデータが同じフォーマットで集計されている Excel です。訪日外客数の通年の月別・市場別の推移と伸率 (前年同月比) の表を含むシートが年別に格納されています。
市場別≒国別。台湾とかマカオとかがあるので「市場」となっていると思われますが、このブログでは便宜上「国」扱いします。
この Excel ブックから各年 (各シート) のデータを「まとめて」 Power BI Desktop に取り込む方法を紹介します。
操作手順
- [データを取得] -> [すべて] (または [ファイル]) -> [Excel] -> [接続] をクリックし、ダウンロードした Excel ブックを選択します。
もしくは、[データを取得] -> [すべて] (または [その他]) -> [Web] -> [接続] をクリックし、URL (https://www.jnto.go.jp/jpn/statistics/since2003_tourists.xlsx) を入力します。
※ [Web] を使うと、後々データが更新されたときに、[最新の情報に更新] ボタン 1 つで新しいデータを取り込めるので、都度ファイルをダウンロードする手間が省けます。
- 各シートと、各シートの印刷領域が表示されます。印刷領域は不要ですので 2003 から 2017 にだけチェックを入れて読み込みたくなりますが、ここで裏技！ファイル名を右クリックして [編集] をクリックします。こうすることで 読み込むデータの選択をクエリで設定し、シートの増減に柔軟に対応できるようにします。
- クエリ エディターが開きます。Name 列に対しテキスト フィルター [次の値で始まらない] を設定します。
※ 不要なものを取り除くように設定することで、この先 2018、2019、…とシートが増えることに対応できます。
- アンダーバーで始まる印刷領域は不要なので「_」を入力し [OK] をクリックします。
- 2003 から 2017 のシートだけに絞り込まれました。
シート名 (年) を表す Name 列と肝心のデータを含む Data 列を選択し、列名を右クリックして [その他の列を削除] します。
※ 複数の列を選択するには、2つ目以降の列を選択するときに Ctrl キーを押しながらクリックします。
- さらに、[Name] 列のプルダウン メニューをクリックし、昇順で並び替えます。
(この手順は、後でデータ加工をする時のために行っています。本投稿のみにおいては意味はありません。)
- この状態で [Data] 列の右側の矢印アイコンをクリックします。
- [元の列名をプレフィックスとして使用します] のチェックを外して [OK] をクリックします。
この設定はしなくても構いませんが、列名が冗長になるのを避けています。
以上の手順で、2003 から 2017 の各シートのデータを、1つのデータとして取り込めました。
取り込んだデータには不要な行や列がたくさんあるので、次回はそれらを処理する方法を紹介します。
なお、同じフォーマットの Excel ファイルが複数ある場合は、データ接続で [フォルダ] を選択してバイナリの結合を行います。バイナリの結合では、ファイルの種類と構造が同じであれば、特定のフォルダー内のすべてのバイナリを簡単に結合できます。詳細はこちらのドキュメントをご覧ください。
Power BI Desktop でバイナリを結合する
https://powerbi.microsoft.com/ja-jp/documentation/powerbi-desktop-combine-binaries/
ちょっと分かりにくいので、こちらも追って記事にしたいと思います。