(Power BI) Mから始めよう #11 Excel 複数のブックの複数のシートのデータを取り出す

（この記事は、2021/12/08に執筆し、2022/1/29に修正、追記しました）
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/7fc37de1-fabb-def9-18ae-963a7eeac980.png)

　フォルダー内のファイルのデータを取得するには、「データを取得」からフォルダーを選ぶと思いますが、この操作を行うとヘルパークエリなどが勝手に出来上がってしまいます。このヘルパークエリの中身については、別途考えるとして、Power Queryを直接書いて操作を行っていきたいと思います。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/f0d961d6-49f6-b5bc-f017-b2934cccdb57.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/9a696410-86f1-8ff1-025f-3a97ab0c07d3.png)

　また、画面操作で項目の項目の展開を行うと、後で項目が増えたときにコードの書き換えをしなければなりません。項目がどれだけあるか気にせずにまとめる方法を説明します。

# パラメータを作成
　まず最初に、フォルダーの位置をパラメータ "folder" に設定しておきます。後で変更になりそうな値は、コード中に直接書き込まずに、パラメータを使ってコードの外に出しておきましょう。
　PCに詳しい方は、環境変数を使う方法を思いつくと思いますが、Power QueryでPCの環境変数を参照することはできません。私もかつて数時間かけて調べましたが、Power BIサービスに発行したときに、意味のない操作になると気づいて諦めました。また、セキュリティ面からも、そのようなデータへのアクセス方法は望ましくありません。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/862de5a1-8210-4e9e-b863-283218ecffa6.png)

# シートのデータだけをまとめる Table.Combine
　ブック名やシート名が必要なく、データだけササッとほしいという場合は、Table.Combileでまとめてしまいましょう。

## Folder.Files
　フォルダー内のファイルを取得するには、**Folder.Files**を使用します。

```
let
    Source = Folder.Files(folder)
in
    Source
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/f8d41972-c59f-7242-8972-7e4c6123e87d.png)

## Excel.Workbookでエクセルデータソースへアクセス
 Binaryをエクセルファイルとして読めるようにします。エクセルの表の１行目を項目名にするので、Excel.Workbookの第２引数は true にしておきます。

```text
    // Excelデータソースへのアクセス
    Workbook = 
        Table.TransformColumns(
            Source,
            {
                "Content",
                each
                    Excel.Workbook(_, true)
            }
        )
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/717a19d1-a029-f731-ef4b-23afea0ecaee.png)

## シートをばらす

 Table.ExpandTableColumnを使って、シートのデータが入っているDataを取り出します。

```text
    // シートをばらす
    Expand = 
        Table.ExpandTableColumn(
            Workbook, 
            "Content", 
            {"Data"}, 
            {"Data"}
        )
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/d5dd1ab9-5625-1666-b3a1-f8e09ab4085c.png)

## 各シートのデータを１つの表にまとめる
　Table.Combineを使うと、項目名を指定することなく、表を結合することができます。

```text
    // 各シートのデータを結合
    Combine = Table.Combine(Expand[Data])
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/f8830e90-503a-d5c3-e167-3c262203f20c.png)

## まとめ
　コードをすべてまとめると以下のようになります。

```text
let
    Source = Folder.Files(folder),
    // Excelデータソースへのアクセス
    Workbook = 
        Table.TransformColumns(
            Source,
            {
                "Content",
                each
                    Excel.Workbook(_, true)
            }
        ),
    // シートをばらす
    Expand = 
        Table.ExpandTableColumn(
            Workbook, 
            "Content", 
            {"Data"}, 
            {"Data"}
        ),
    // 各シートのデータを結合
    Combine = Table.Combine(Expand[Data])

in
    Combine
```

# ファイル名、シート名を残してシートの項目をまとめる１ Table.ExpandTableColumn

## Folder.Files
　前と同じように、フォルダー内のファイルを取得するには、**Folder.Files**を使用します。

```
let
    Source = Folder.Files(folder)
in
    Source
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/f8d41972-c59f-7242-8972-7e4c6123e87d.png)

## 項目の整理
　ContentのBinaryの中に、エクセルファイルが入っています。ここでは、ContentとNameだけを残して、あとは削除します。また、NameはFileNameに変更し、項目の並べ替えも行います。

```text
    // Content(エクセルデータ)とName(ファイル名)以外を削除
    RemoveOtherColumns = 
            Table.SelectColumns(
                Source,
                {"Content", "Name"}
            ),
    // NameをFileNameに変更
    Rename = Table.RenameColumns(
        RemovedOtherColumns,
        {"Name", "FileName"}
    ),
    // ファイル名を１番目に移動
    Reorder = Table.ReorderColumns(
        Rename,
        {
            "FileName",
            "Content"
        }
    )
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/19029777-9dee-e799-178d-4dc55b4c4e2c.png)

## Excel.Workbookでエクセルデータソースへアクセス
　Contentにあるエクセルファイルを開いていきます。エクセルの表の１行目を項目名にするので、Excel.Workbookの第２引数は true にしておきます。

```
    Workbook = 
        Table.TransformColumns(
            Reorder, 
            {
                "Content", 
                each Excel.Workbook(_, true)
            }
        )
````
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/096ee000-a5cb-8804-eb28-46acd3ae0cd2.png)


## ワークシート展開 ExpandTableColumnとPromoteHeaders
　ワークシートの項目を展開すると、以下のようになります。

```text
let
    ・・・ここは前と同じ・・・・
    ExpandSheets = 
        Table.ExpandTableColumn(
            Workbook, 
            "Content", 
            {"Name", "Data"}, 
            {"SheetName", "Data"}
        )
in
    ExpandSheets
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/35392a58-48c3-5a45-2db2-b7f07c2d0a11.png)

　**※特定のシート名のみを対象にしたい場合は、この後にSheetNameにフィルターをかける処理を挟みます。**

## 通常のマウス操作で展開すると融通が利かない

　このDataをそのまま展開すると以下のようになります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/b7248cb3-bad8-a183-854f-9ee62e74743b.png)

```
let
    ・・・ここは前と同じ・・・・
    #"Expanded {0}" = 
            Table.ExpandTableColumn(
                ExpandSheets, 
                "Data", 
                {
                    "col1", 
                    "col2"
                }, 
                {
                    "col1", 
                    "col2"
                }
            )
in
    #"Expanded {0}"
```

　エクセルデータには、col3やcol4もありますが、最初に読み込んだものにはcol1とcol2しかなかったため、他の列は展開されませんでした。展開する前に、「さらに読み込み...」としてやれば、col3、col4も展開してくれますが、項目が増えた場合、その都度この操作をやりなおさなければなりません。
　項目名を指定せず全て展開するには、次のようにコードを書きなおします。

##　全ての項目名を足したリストを使った結合
　ブック名やシート名を残しておきたい場合は、Table.ExpandTableColumnでDataを展開します。その際、展開する項目のリストを作成して展開します。

```text
    Expand = 
        Table.ExpandTableColumn(
            ExpandSheets,
            "Data",
            List.Union(                       // 全ての表の項目名の和集合を作成
                List.Transform(               // 各表の項目名のリストを返す
                    ExpandSheets[Data],
                    each Table.ColumnNames(_)
                )
            )
        )
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/e012ce38-76fa-42ae-94ab-448e0cc20d69.png)

## まとめ

```
let
    Source = Folder.Files(folder),
    // ファイル名とデータ項目を取得
    RemovedOtherColumns = 
        Table.SelectColumns(
            Source,
            {"Content", "Name"}
        ),
    // NameをFileNameに変更
    Rename = Table.RenameColumns(
        RemovedOtherColumns,
        {"Name", "FileName"}
    ),
    // FileNameを１番目に移動
    Reorder = Table.ReorderColumns(
        Rename,
        {
            "FileName",
            "Content"
        }
    ),
    // Excelデータソースへのアクセス
    Workbook = 
        Table.TransformColumns(
            Reorder, 
            {
                "Content", 
                each Excel.Workbook(_, true)
            }
        ),
    // Contentからシート名とシートデータを取得
    ExpandSheets = 
        Table.ExpandTableColumn(
            Workbook, 
            "Content", 
            {"Name", "Data"}, 
            {"SheetName", "Data"}
        ),
    // シートデータを結合
    Expand = 
        Table.ExpandTableColumn(
            ExpandSheets,
            "Data",
            List.Union(                       // 個別のリスト項目の和集合を作成
                List.Transform(               // 変換関数をリストに適用して新しいリストを返す
                    ExpandSheets[Data],
                    each Table.ColumnNames(_)
                )
            )
        )
in
    Expand
```
# ファイル名、シート名を残してシートの項目をまとめる２ Table.AddColumnでファイル名を入れ込む
　CurbalのYoutubeチャンネルで、ファイル名をAddColumnで埋め込んでいく方法が解説してありました。

https://www.youtube.com/watch?v=hJF2dPfIeaM

　ちょっとトリッキーかと思いましたが、その手法を使ってやってみました。

```text
let
    Source = Folder.Files(folder),
    // Excelデータソースへのアクセス
    Step1 = Table.TransformColumns(
        Source,
        {
            "Content",
            each Excel.Workbook(_, true)
        }
    ),
    // Contentにファイル名を埋め込む
    Step2 = Table.AddColumn(
        Step1,
        "Workbooks",
        (x)=> 
            Table.AddColumn(
                x[Content],
                "File",
                each x[Name]
            )
    ),
    // ファイル名が埋め込まれたテーブルを結合
    Step3 = Table.Combine(
        Step2[Workbooks]
    ),
    // Dataにファイル名、シート名を埋め込む
    Step4 = Table.AddColumn(
        Step3,
        "Worksheets",
        (x)=>
            Table.AddColumn(
                x[Data],
                "File Sheet",
                each [File = x[File], Sheet = x[Name]]
            )
    ),
    // ファイル名、シート名が埋め込まれたテーブルを結合
    Step5 = Table.Combine(Step4[Worksheets]),
    // ファイル名、シート名を展開
    Step6 = Table.ExpandRecordColumn(
        Step5,
        "File Sheet",
        {"File", "Sheet"}
    )
in
    Step6
```

　Step2のところで、追加されたWorkbooksのテーブルの中にファイル名を埋め込んでます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/603f0d0a-bf08-ca2a-d3fc-ad8ab3b665d3.png)
　それをすべて結合すると、
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/0034ded2-62a3-baec-c2cf-2d957c35686c.png)
　Dataの中に、シートに登録されたデータがあるので、この中にファイル名とシート名を埋め込みます。別々にやるとスマートじゃないので、Step4では、レコード型で埋め込んでいます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/6c93422b-861c-ff58-498d-bd2463882c84.png)

　それを、Step5で全て結合し、Step6でStep4で埋め込んだファイル名、シート名をばらしています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/17122ca4-8a8e-0733-5691-3cc8e8a211ee.png)

 Step2とStep4で行った、外側の値を内側のテーブルに埋め込む方法ですが、Curbalでは以下のような書き方になっていました。

```text:Step2
    // Contentにファイル名を埋め込む
    Step2 = Table.AddColumn(
        Step1,
        "Workbooks",
        each 
            Table.AddColumn(
                [Content],
                "File",
                (x)=> [Name]
            )
    ),
```

```text:Step4
    // Dataにファイル名、シート名を埋め込む
    Step4 = Table.AddColumn(
        Step3,
        "Worksheets",
        each
            Table.AddColumn(
                [Data],
                "File Sheet",
                (x)=> [File = [File], Sheet = [Name]]
            )
    ),
```

　これだと、なにをやってるのかわかりにくいので、以下のように書き換えてみました。

```text:Step2
    // Contentにファイル名を埋め込む
    Step2 = Table.AddColumn(
        Step1,
        "Workbooks",
        (x)=> 
            Table.AddColumn(
                x[Content],
                "File",
                each x[Name]
            )
    ),
```

　外側のTable.AddColumnのカラム・ジェネレーターで作成された値を**x**で受け、内側のTable.AddColumnのカラム・ジェネレーターで使っています。xには、ファイル名他の外側の列の値がレコードで入っているので、内側のテーブルにファイル名の列を作成することができます。
　つまり、Curbalの方法で書かれたコードを更に冗長に書くと以下のようになります。

```text:Step2
    // Contentにファイル名を埋め込む
    Step2 = Table.AddColumn(
        Step1,
        "Workbooks",
        each
            Table.AddColumn(
                _[Content],
                "File",
                (x)=> _[Name]
            )
    ),
```
　外側のTable.AddColumnのカラム・ジェネレーターはeachになっているので、レコードの値は **_** に入ります。これは省略可能なので **_[Content]** の代わりに **[Content]** と書くことができます。内側のTable.AddColumnのカラム・ジェネレーターでeachを使うと、**_** の値が変わってしまうので **(x)** （カッコの中の変数はなんでもよい）を使って違う変数に値を入れ、**_[Name]** で外側のファイル名を内側で使っています。

# 参考

- MYNDA TREACY , Import Multiple Files Containing Multiple Sheets with Power Query, 2020.04.02, my Onlinetraining hub, https://www.myonlinetraininghub.com/import-multiple-files-containing-multiple-sheets-with-power-query
- しょうた, Excel PowerQuery 列の展開を全てまとめて実施する方法, 2021.08.10, Excel Excellent Technics, https://excel-excellent-technics.com/excel-powerquery-table-expandtablecolumn-11572