Power Queryで複数のExcelブックの共通しない名称のシートを読み込む

#Power Queryとは

モダンExcelやPower BIに欠かせない[ETLツール](https://ja.wikipedia.org/wiki/Extract/Transform/Load)である「Power Query」、Excel VBAのコードを覚えることなく、VBAで出来るようなことや出来ないようなこともがポイント&クリックで可能となるツールである。

#この記事でやること

- 複数のExcelブックが存在する。
- 各Excelブックに含まれるシートの名称は統一されていない。
- 各Excelブックに含まれるシートの数も統一されていない。
- 全ブックおよび全シートのデータを1つのテーブルに統合する
- **操作にコードを使わない（重要）**

#ビジネス的な与件
何かのイベントの参加者だとか、会の登録だとかをExcelで集約すると考えてほしい。
そもそも**Excel使うな**、**[PowerApps](https://powerapps.microsoft.com/ja-jp/)使えとかいう指摘はご尤**もだが、**話が進まないのでここでは無視する**。

こんな感じのExcelと考えてほしい。IDと氏名の列があり、人数に応じて参加者が追加されていく。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324028/baee09f4-1388-4022-8b22-fab2ceb85b18.png" width =50%>

シートを増やすな、シート名を変えるなといくら連絡をしたとしても、**守らない人は絶対にいる**。
**悲しいが、人はルールを守れない生き物なのである**。

以下のような感じでシート名が一部の人に編集されていたり、増やされているとしよう。Excelを開いたとたん、対処法を持たない事務局は絶望するであろう。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324028/c6a62dd4-abfb-522a-9ee4-f4d0136ba792.png" width =50%>

と、ばらばらのシート名、シート数であっても、すべてをノーコーディングで一つのテーブルとする手順について解説する。

#手順1/2　複数のExcelブック内の同一名称のシートを統合する。
手順の前半は、「同一名称のシートを統合する」のと同様の手順となる。
まず、ExcelもしくはPower BIでPower Queryを起動し、「フォルダー」からデータを取得する。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324028/b1783a31-4d33-a0ea-845f-425ed61fab5e.png" width =80%>
※画面はフォルダだが、SharePointフォルダでも可能。
※ただしExcelのPower Queryだと、UIにSharePointフォルダが無いかもしれない。Power BI Desktopをダウンロードすべし。

パスを指定して読み込むと、該当のフォルダに含まれるファイルの一覧が表示される。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324028/50ab7493-fdf5-4ff1-2cd3-039d24668536.png" width =100%>

いずれかのブックのシートを読み込みサンプルをするか聞かれるので、任意のファイルを選択する。
（選択しなくても、勝手にいずれかのブックとシートが選択される）
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324028/b666dda5-a4b8-ac75-a23b-8a3d204a24cc.png" width =80%>

ここでサンプルとして指定したシートの名称が存在しないブックがフォルダに入っていると、以下のエラーが発生する。
※上記の画面で「エラーのあるファイルをスキップする」にチェックを入れているとエラーが発生せず、サンプルシートと同一名称のシートがすべて結合される。
![07.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324028/ec1325df-5c2d-f8d4-627e-eb4ec70ccc66.png)

#手順2/2　シート名が異なるシートも読み込むように設定する。

上記1/2の手順を完了すると、「サンプルクエリ」というグループと、4つのクエリ・関数・パラメータが自動的に生成されている。
複数のブックから同一名称のシートを読み込むために必要なものである。

その中で、「〇〇からサンプルファイルを変換する」というクエリを開く。
※〇〇には、参照元として指定したフォルダ名称などが通常入る。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324028/98f30af9-19ce-830d-3577-6c02ba499599.png" width=100%>

このクエリは、サンプルファイルからある名称のシートだけを開くように指示しているものであり、このクエリのステップをシート名を問わずに開くように変更することで、最終的に読み込まれるクエリも連動して変更される。

右側の「ステップ」の「ナビゲーション」は、特定の名称のシートのみを開くよう指示しているものなので、「ナビゲーション」以降のステップを、右クリックして「最後まで削除」する。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324028/7878193c-5da7-d9a8-4fd3-eca914b043c9.png" width=50%>

続けて、「Data」列を展開し、すべてのシートを残す…のだが、その前に邪魔になるほかの列を右クリックして削除する。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324028/78f4f07e-dc51-57de-2bcb-caa2ba65a6dc.png" width=70%>

さらに「Data」列の「展開」ボタン（90度曲がった矢印が二つくっついているボタン。**[某氏](https://qiita.com/susumutanaka)によると「調子に乗っちゃって」ボタン**だそう。）を押し、OKを押す。
（元の列名をプレフィックス…は、つけておいてもよいが、列名が長すぎてうっとおしくなるので、外したほうが良い。）
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324028/142448e7-63f9-1168-36a7-bda787ddb388.png" width=50%>

シートの中身が展開されるが、列名が1行目のデータとして入ってしまっているので、「1行目をヘッダーとして使用」をクリックする。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324028/e8b74d2f-6453-cf9f-3a2a-596b36c83073.png" width=50%>

すると、**なんと言うことでしょう…。読み込み後のクエリが異なるシート名を含めて、すべてのExcelブックとシートを読み込んでいるではありませんか！**
![15_1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324028/e081e2f8-e7be-3107-1182-bc56366b959f.png)

複数のシートを含むテーブルを読み込むと、上の画像のようにエラーが生じたり、列名がデータに入ってしまったりするが、適当にエラーを消したりフィルターかけて取り除けばOK。

![18.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/324028/7e89a733-b42a-4e7a-6431-ba9e8dcab40b.png)

#Excelをやめられない事務局に救いを


**Excelを親の仇ほど憎んでいる**人はたくさんいると思うが、**実務上、まだまだ事務局作業にExcelを使わざるを得ないケースもある**。

[Microsoft Flow](https://flow.microsoft.com/ja-jp/)を組み合わせてSharePointやオンプレミスに自動的に格納したり、格納されたデータをPower Queryで読み取ったりといった形で自動化を進めることで、そのような人たちが救われることを願う。