Power Queryとは
モダンExcelやPower BIに欠かせないETLツールである「Power Query」、Excel VBAのコードを覚えることなく、VBAで出来るようなことや出来ないようなこともがポイント&クリックで可能となるツールである。
この記事でやること
- 複数のExcelブックが存在する。
- 各Excelブックに含まれるシートの名称は統一されていない。
- 各Excelブックに含まれるシートの数も統一されていない。
- 全ブックおよび全シートのデータを1つのテーブルに統合する
- 操作にコードを使わない（重要）
ビジネス的な与件
何かのイベントの参加者だとか、会の登録だとかをExcelで集約すると考えてほしい。
そもそもExcel使うな、PowerApps使えとかいう指摘はご尤もだが、話が進まないのでここでは無視する。
こんな感じのExcelと考えてほしい。IDと氏名の列があり、人数に応じて参加者が追加されていく。
シートを増やすな、シート名を変えるなといくら連絡をしたとしても、守らない人は絶対にいる。
悲しいが、人はルールを守れない生き物なのである。
以下のような感じでシート名が一部の人に編集されていたり、増やされているとしよう。Excelを開いたとたん、対処法を持たない事務局は絶望するであろう。
と、ばらばらのシート名、シート数であっても、すべてをノーコーディングで一つのテーブルとする手順について解説する。
手順1/2 複数のExcelブック内の同一名称のシートを統合する。
手順の前半は、「同一名称のシートを統合する」のと同様の手順となる。
まず、ExcelもしくはPower BIでPower Queryを起動し、「フォルダー」からデータを取得する。
※画面はフォルダだが、SharePointフォルダでも可能。
※ただしExcelのPower Queryだと、UIにSharePointフォルダが無いかもしれない。Power BI Desktopをダウンロードすべし。
パスを指定して読み込むと、該当のフォルダに含まれるファイルの一覧が表示される。
いずれかのブックのシートを読み込みサンプルをするか聞かれるので、任意のファイルを選択する。
（選択しなくても、勝手にいずれかのブックとシートが選択される）
ここでサンプルとして指定したシートの名称が存在しないブックがフォルダに入っていると、以下のエラーが発生する。
※上記の画面で「エラーのあるファイルをスキップする」にチェックを入れているとエラーが発生せず、サンプルシートと同一名称のシートがすべて結合される。
手順2/2 シート名が異なるシートも読み込むように設定する。
上記1/2の手順を完了すると、「サンプルクエリ」というグループと、4つのクエリ・関数・パラメータが自動的に生成されている。
複数のブックから同一名称のシートを読み込むために必要なものである。
その中で、「〇〇からサンプルファイルを変換する」というクエリを開く。
※〇〇には、参照元として指定したフォルダ名称などが通常入る。
このクエリは、サンプルファイルからある名称のシートだけを開くように指示しているものであり、このクエリのステップをシート名を問わずに開くように変更することで、最終的に読み込まれるクエリも連動して変更される。
右側の「ステップ」の「ナビゲーション」は、特定の名称のシートのみを開くよう指示しているものなので、「ナビゲーション」以降のステップを、右クリックして「最後まで削除」する。
続けて、「Data」列を展開し、すべてのシートを残す…のだが、その前に邪魔になるほかの列を右クリックして削除する。
さらに「Data」列の「展開」ボタン（90度曲がった矢印が二つくっついているボタン。某氏によると「調子に乗っちゃって」ボタンだそう。）を押し、OKを押す。
（元の列名をプレフィックス…は、つけておいてもよいが、列名が長すぎてうっとおしくなるので、外したほうが良い。）
シートの中身が展開されるが、列名が1行目のデータとして入ってしまっているので、「1行目をヘッダーとして使用」をクリックする。
すると、なんと言うことでしょう…。読み込み後のクエリが異なるシート名を含めて、すべてのExcelブックとシートを読み込んでいるではありませんか！
複数のシートを含むテーブルを読み込むと、上の画像のようにエラーが生じたり、列名がデータに入ってしまったりするが、適当にエラーを消したりフィルターかけて取り除けばOK。
Excelをやめられない事務局に救いを
Excelを親の仇ほど憎んでいる人はたくさんいると思うが、実務上、まだまだ事務局作業にExcelを使わざるを得ないケースもある。
Microsoft Flowを組み合わせてSharePointやオンプレミスに自動的に格納したり、格納されたデータをPower Queryで読み取ったりといった形で自動化を進めることで、そのような人たちが救われることを願う。