[Power BI Query Tips] Excel の複数シートを効率的に取り込む方法

  • [アーティクル]

Microsoft Japan Business Intelligence Tech Sales Team 伊藤

Power BI Desktop はクエリ エディターで [クエリの追加] を使うことで、同じフォーマットの複数のデータ (クエリ) を結合することができます。データベース用語でいうところの UNION ALL や APPEND の処理です。

clip_image001

1 つの Excel ファイルから取り込む場合、シートごとに同じようなクエリを作って [クエリの追加] を行うのではなく、もっとスマートに結合できます。今回はその方法を日本政府観光局 (JNTO) のファイル https://www.jnto.go.jp/jpn/statistics/since2003_tourists.xlsx を使って説明します。 clip_image002

こちらは、年別のデータが同じフォーマットで集計されている Excel です。訪日外客数の通年の月別・市場別の推移と伸率 (前年同月比) の表を含むシートが年別に格納されています。

市場別≒国別。台湾とかマカオとかがあるので「市場」となっていると思われますが、このブログでは便宜上「国」扱いします。

この Excel ブックから各年 (各シート) のデータを「まとめて」 Power BI Desktop に取り込む方法を紹介します。

操作手順

  1. [データを取得] -> [すべて] (または [ファイル]) -> [Excel] -> [接続] をクリックし、ダウンロードした Excel ブックを選択します。
    もしくは、[データを取得] -> [すべて] (または [その他]) -> [Web] -> [接続] をクリックし、URL (https://www.jnto.go.jp/jpn/statistics/since2003_tourists.xlsx) を入力します。
    ※ [Web] を使うと、後々データが更新されたときに、[最新の情報に更新] ボタン 1 つで新しいデータを取り込めるので、都度ファイルをダウンロードする手間が省けます。
    clip_image003
  2. 各シートと、各シートの印刷領域が表示されます。印刷領域は不要ですので 2003 から 2017 にだけチェックを入れて読み込みたくなりますが、ここで裏技！ファイル名を右クリックして [編集] をクリックします。こうすることで 読み込むデータの選択をクエリで設定し、シートの増減に柔軟に対応できるようにします。clip_image004
  3. クエリ エディターが開きます。Name 列に対しテキスト フィルター [次の値で始まらない] を設定します。
    ※ 不要なものを取り除くように設定することで、この先 2018、2019、…とシートが増えることに対応できます。clip_image005
  4. アンダーバーで始まる印刷領域は不要なので「_」を入力し [OK] をクリックします。
    clip_image006
  5. 2003 から 2017 のシートだけに絞り込まれました。
    シート名 (年) を表す Name 列と肝心のデータを含む Data 列を選択し、列名を右クリックして [その他の列を削除] します。
    ※ 複数の列を選択するには、2つ目以降の列を選択するときに Ctrl キーを押しながらクリックします。clip_image007
  6. さらに、[Name] 列のプルダウン メニューをクリックし、昇順で並び替えます。
    (この手順は、後でデータ加工をする時のために行っています。本投稿のみにおいては意味はありません。)
    image
  7. この状態で [Data] 列の右側の矢印アイコンをクリックします。
    image
  8. [元の列名をプレフィックスとして使用します] のチェックを外して [OK] をクリックします。
    image
    この設定はしなくても構いませんが、列名が冗長になるのを避けています。

以上の手順で、2003 から 2017 の各シートのデータを、1つのデータとして取り込めました。

image

取り込んだデータには不要な行や列がたくさんあるので、次回はそれらを処理する方法を紹介します。

なお、同じフォーマットの Excel ファイルが複数ある場合は、データ接続で [フォルダ] を選択してバイナリの結合を行います。バイナリの結合では、ファイルの種類と構造が同じであれば、特定のフォルダー内のすべてのバイナリを簡単に結合できます。詳細はこちらのドキュメントをご覧ください。

Power BI Desktop でバイナリを結合する
https://powerbi.microsoft.com/ja-jp/documentation/powerbi-desktop-combine-binaries/

ちょっと分かりにくいので、こちらも追って記事にしたいと思います。