Power Query ヘルパークエリで複数のエクセルブックのデータをまとめてみよう ：Mから始めよう #15

(2022/02/18に記述しました)

　#14のように、まとめようとするエクセルファイルの形式が異なっている場合、どのようにまとめていくかを考えます。
　スライドは、[Speaker Deck](https://speakerdeck.com/fukuyori/hu-falsexue-20220219)を参照してください。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/877e1646-18dd-918a-759e-c0f67a8a4ead.png)

# 元のファイルと目標のデータテーブル
　文部科学省の[学校基本調査](https://www.mext.go.jp/b_menu/toukei/chousa01/kihon/1267995.htm)のデータを[e-Stat](https://www.e-stat.go.jp/stat-search/files?page=1&toukei=00400001&tstat=000001011528)からダウンロードして使っていきます。
　今回使うエクセルファイルを見てみると、以下のようなものがありました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/267faedd-373e-e2c0-b8af-2df8b2f69eb9.png)

　1つ目はA1から始まる表になってて使いやすそうです。その次は、分類が増えて見栄えもよくした感じで、最後のはやたら空の列や行を入れてます。これを以下のようなデータテーブルに変換したいと思います。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/2f3c80b9-c1e6-19fa-a824-112a10b94dee.png)

# 1.フォルダーパスをパラメータに設定
　対象のエクセルファイルが入ったフォルダーのパスを、パラメータに設定します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/355efdd3-ece5-6fd3-3942-f791bc14c7bb.png)

# 2.フォルダー内のファイルを表示
　フォルダー内のファイルを表示するクエリーを作成します。

```:Query
let
    Source = Folder.Files(FolderPath)
in
    Source
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/5f401b78-645b-96e7-0779-e626b73e736f.png)

# 3.サンプルファイルを作成
　2で表示された1番目のエクセルファイル(1976.xls)をサンプルファイルとして登録します。

```text:SampleFile1976
let
    Source = Folder.Files(FolderPath){[Name="1976.xls"]}[Content]
in
    Source
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/be08fda5-8cf3-1dd1-c501-d1b52939cddc.png)

# 4.サンプルファイルへのパラメータを作成
　サンプルファイルを加工するクエリを作る前に、パラメータを作成します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/eaecab71-8f02-6e7e-d7d3-7f73b760b6a5.png)

 このパラメータは、サンプルファイルを渡すためのものなので、種類は**バイナリ**にします。

# 5.サンプルファイルの変換クエリを作成
　変換作業を行う前に、サンプルファイルを読み込む部分を書きます。

```text:ConvertSampleFile1976
let
    Source = Excel.Workbook(Parameter1976),
    WorkSheet = Source{0}[Data]
in
    WorkSheet
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/88f797e9-4440-f31b-c994-307a1107ee87.png)

- Parameter1976 →パラメータで入力ファイルを指定
- {0} → ここでは、データの入っているシートは1番目のものを指定
- [Data] → Data(シート内のデータ)とName(シート名)の2つの項目のうちDataを指定

# 6.変換作業を記述していく
　サンプルファイルを読み込んだ後の処理をGUIで操作していきます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/2426dd47-8617-2c8e-6252-6dd6b3e6a485.png)

```text:ConvertSampleFile1976
let
    Source = Excel.Workbook(Parameter1976){0}[Data],
    PromotedHeaders = 
        Table.PromoteHeaders(Source, 
        [PromoteAllScalars=true]
    ),
    ChangedType = 
        Table.TransformColumnTypes(
            PromotedHeaders,{
                {"区分", type text}, 
                {"国立", Int64.Type}, 
                {"公立", Int64.Type}, 
                {"私立", Int64.Type}
            }
        ),
    UnpivotedOtherColumns = 
        Table.UnpivotOtherColumns(
            ChangedType, 
            {"区分"}, 
            "属性", 
            "値"
        ),
    // エラー値を0に置換
    ReplacedErrors = 
        Table.ReplaceErrorValues(
            UnpivotedOtherColumns, 
            {{"値", 0}}
        )
in
    ReplacedErrors
```

# 7.関数の作成
　出来上がったクエリを右クリックし、関数の作成を選択し、関数を作ります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/3b3ab12b-e17c-8146-3b6f-1db091eb3312.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/7fc878a5-f18d-c787-8dc7-381ac0691953.png)

# 8.各ファイルに関数を使ってみる
　最初に作成したフォルダー内のファイルを表示させるクエリに、関数を適用する項目を追加します。

```text:Query
let
    Source = Folder.Files(FolderPath),
    // Excelからテーブルを取得
    Step1 = Table.TransformColumns(
        Source,
        {
            "Content",
            ConvertFx1976
        }
    )
in
    Step1
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/a287a11d-2fd2-a264-35e0-656f5b989d79.png)

　1行目は正しく変換されていますが、2行目はエラーとなっています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/40a2963b-05a5-631d-240a-e09b56bb3d8b.png)

# 9.別の関数を作成
　最初の関数では処理できないので、新しく関数を作成するため、３～７を行います。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/36493e99-25de-1f0b-e9f2-29506520c243.png)

　ここで作成したクエリは以下のようなものでした。

```text:ConvertSampleFile1977
let
    // エクセルの最初のシートを開く
    Source = Excel.Workbook(Parameter1977){0}[Data],
    ChangedType = 
        Table.TransformColumnTypes(
            Source,
            {
                {"Column1", type text}
            }
        ),
    // 有効な行を選択
    FilteredRows = 
        Table.SelectRows(
            ChangedType, 
            each (
                [Column1] = "区分" or 
                [Column1] = "男女ともにいる学校" or 
                [Column1] = "男のみの学校" or 
                [Column1] = "女のみの学校" or 
                [Column1] = "生徒のいない学校"
            )
        ),
    // 1行目をヘッダーに
    PromotedHeaders = 
        Table.PromoteHeaders(
            FilteredRows, 
            [PromoteAllScalars=true]
        ),
    FilteredRows1 = 
        Table.SelectRows(
            PromotedHeaders,
            each ([区分] <> "区分")
        ),
    // 不要な列を削除
    RemovedOtherColumns = 
        Table.SelectColumns(
            FilteredRows1,
            {"区分", "国立", "公立", "私立"}
        ),
    // データ型を指定
    ChangedType1 = 
        Table.TransformColumnTypes(
            RemovedOtherColumns,
            {
                {"区分", type text}, 
                {"国立", Int64.Type}, 
                {"公立", Int64.Type}, 
                {"私立", Int64.Type} 
            }
        ),
    // ピボット解除
    UnpivotedOtherColumns = 
        Table.UnpivotOtherColumns(
            ChangedType1, 
            {"区分"}, 
            "属性", 
            "値"
        ),
    // エラー値を0に置換
    ReplacedErrors = 
        Table.ReplaceErrorValues(
            UnpivotedOtherColumns, 
            {{"値", 0}}
        )
in
    ReplacedErrors
```

# 10.再度関数を当ててみる
　クエリーを書き換えて、もう一度確認してみます。

```text:Query
let
    Source = Folder.Files(FolderPath),
    // Excelからテーブルを取得
    Step1 = Table.TransformColumns(
        Source,
        {
            "Content",
            each 
                try ConvertFx1976(_)
                otherwise ConvertFx1977(_)
        }
    )
in
    Step1
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/894ff4dc-c084-58d1-cd9c-63f3e0a757f3.png)

　まだ1行、エラーが発生しています。

# 11.3つ目の関数を作成して当ててみる
　３～７を繰り返して関数を作ります。
　クエリに新たに作った関数を追加して適用させます。

```text:Query
let
    Source = Folder.Files(FolderPath),
    // Excelからテーブルを取得
    Step1 = Table.TransformColumns(
        Source,
        {
            "Content",
            each 
                try ConvertFx1976(_)
                otherwise try ConvertFx1977(_)
                otherwise ConvertFx2008(_)
        }
    )
in
    Step1
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/7d006c6b-cae1-82d6-695c-a8345ccc9708.png)
# 12.テーブルを結合させて完成
　クエリの続きを記述し、完成です。

```text:Query
let
    Source = Folder.Files(FolderPath),
    // Excelからテーブルを取得
    Step1 = Table.TransformColumns(
        Source,
        {
            "Content",
            each 
                try 
                    ConvertFx1976(_)
                otherwise  try 
                    ConvertFx1977(_)
                otherwise
                    ConvertFx2008(_)
        }
    ),
    // ファイル名をエクセル表に追加して結合
    Step2 = Table.AddColumn(
        Step1,
        "Workbooks",
        (x)=>
            Table.AddColumn(
                x[Content],
                "Year",
                each Number.FromText(Text.Select(x[Name],{"0".."9"})),
                Int64.Type
            )
    ),
    Step3 = Table.Combine(Step2[Workbooks])
in
    Step3
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/7364ce59-ad12-2a23-d870-b7447ee46c83.png)

　このデータを使って以下のグラフを作成しました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/cf1eecef-c278-dc8b-570e-96955e2dac46.png)

# 注意点
　今回は、作成した関数が正常化エラーかで判断していますが、エラーにならないけど正常値を返さないパターンもあると思います。その場合は、どちらでも正常値になるようにクエリ内の処理を追加するか、エラーをわざと発生させるかの対応が必要になります。