はじめに
Excelシートの1行目に「シートに関する情報」や「データベースから指定データをダウンロードする際の条件」などが入力されていて、ビジュアル化に使いたい「列のタイトル」や「数値データ」が2行目以降に入力されていることは良くある。（下図の場合は、5行目に「列のタイトル」、6行目以降に「数値データ」が入力されている。）
「ファイル指定」で1つのExcelファイル中の1つのシートを指定して読み込む場合は、Power Queryエディターでデータテーブル中の不要な列や行を削除すれば良いのだが、「フォルダ指定」でも同じ操作が行えるという事実を知らない人が多く、「フォルダ指定」で複数のExcelファイルを一括で読み込むための準備作業として、わざわざフォルダ内の複数のExcelファイルのシートを手作業で一つ一つ編集している事例にたびたび遭遇している。
「2024年！初アウトプットをしよう」の企画に背中を押されて、人生初のQiita投稿をしてみようと思う。
使用データ
PCのデスクトップに作成した「実務DATA」フォルダ内に、以下の3つのExcelファイルを保存している。
- 2024年01月_実務DATA.xlsx
- 2024年02月_実務DATA.xlsx
- 2024年03月_実務DATA.xlsx
上記の各Excelファイルには、シート名が「DATA」のシート（以下「DATA」シートと記載する）のみがある。（冒頭で示した画像は、「2024年01月_実務DATA.xlsx」ファイルの「DATA」シート。）
「ファイル指定」でのPower Queryエディター画面
Power BI Desktopで以下の操作を行い、Power Queryエディターを起動すると、下図の画面が表示される。
- 「ホーム」タブの「データを取得」をクリックする。
- 「データを取得」ウインドウで「Excelブック」を選択し、「接続」ボタンをクリックする。
- 「開く」ウインドウでデスクトップ上の「実務DATA」フォルダを開き、「2024年01月_実務DATA.xlsx」ファイルを選択し、「開く」ボタンをクリックする。
- 「ナビゲーター」ウインドウで「DATA」シートにチェックマークを入れた後、「データの変換」ボタンをクリックすると、下図のようにPower Queryエディターが起動する。
「フォルダ指定」でのPower Queryエディター画面
「フォルダ指定」のデータ読み込み手順と、Power Queryエディターの画面を以下に示す。
-
「ホーム」タブの「データを取得」をクリックする。
-
「データを取得」ウインドウで「フォルダ」を選択し、「接続」ボタンをクリックする。
-
「フォルダー」ウインドウで「参照…」ボタンをクリックしてデスクトップ上の「実務DATA」フォルダを選択し、「OK」ボタンをクリックすると「フォルダー」ウインドウに戻る。
-
「フォルダー」ウインドウで「OK」ボタンをクリックすると、選択したフォルダ名とパス名のウインドウ（c:\User\…\デスクトップ\実務DATA）が表示される。
-
「c:\User\…\デスクトップ\実務DATA」ウインドウの右下に表示される「結合」ボタンのプルダウンメニューから「データの結合と変換」を選択する。
-
「ファイルの結合」ウインドウで「DATA」シートを選択し、「OK」ボタンをクリックすると、下図のようにPower Queryエディターが起動する。
「ファイル指定」と「フォルダ指定」との「クエリ」ペインの比較
下図は、左が「ファイル指定」、右が「フォルダ指定」のPower Queryエディター画面である。
データファイルの読み込みを行うと、読み込んだデータファイルのクエリが各々の画面左端の「クエリ」ペインに表示される。
下図左の「ファイル指定」の場合は、読み込んだデータシート名と同じ名前のクエリ（下図の場合は「DATA」クエリ）が表示されている。
一方、下図右の「フォルダ指定」の場合は、アイコンがフォルダの形をした「その他のクエリ［1］」の下に、読み込んだフォルダ名と同じクエリ（下図の場合は「実務DATA」クエリ）が表示されていると共に、上部に「サンプルファイルの変換」クエリとその関係クエリが表示されているのが分かる。
実は、「フォルダ指定」で複数のデータファイルを一括で読み込むための準備としてExcel上で行っていた手作業の整形を、この「サンプルファイルの変換」クエリで行うことができる。
操作方法は「ファイル指定」で作成されたクエリの場合と同様で、画面上部のメニューも共通である。
終わりに
これまで、社内向けにPower BIの教育を行ってきているが、実務で使用されているExcelシートは、全て「フォルダ指定」かつ「更新」ボタンのワンクリックでデータ更新できるようにクエリを作成できている。
「ファイル指定」で複雑なデータテーブルの編集を行った経験のある方であれば、この投稿に記載した情報を基に、どんなExcelシートでも「事前準備不要の一括更新のクエリ」を作成できると思っているが、「こんな複雑なExcelシートでは、さすがにフォルダ指定での一括読み込みはできないだろう」という事例があれば、私自身のスキルアップのためにご紹介いただければ幸いである。
引き続き、Qiitaへの投稿で、具体的な操作方法についても詳細に紹介していきたい。