Power Queryのヘルパークエリは良いやつだし、カスタム関数はデバッグできる

# 1. はじめに
## 1.1. 誤解
Power Queryを使っていると出てくるヘルパークエリは邪魔だ…。Power Queryのカスタム関数ってデバッグできない…。そんなふうに考えていた時期が俺にもありました。（なお、[昨年も同じ書き出し](https://qiita.com/k_maki/items/08a017d8d480cde28b46#:~:text=%E3%81%9D%E3%82%93%E3%81%AA%E3%81%B5%E3%81%86%E3%81%AB%E8%80%83%E3%81%88%E3%81%A6%E3%81%84%E3%81%9F%E6%99%82%E6%9C%9F%E3%81%8C%E4%BF%BA%E3%81%AB%E3%82%82%E3%81%82%E3%82%8A%E3%81%BE%E3%81%97%E3%81%9F%E3%80%82)だった模様←まるで成長していない）

## 1.2. 正解
ヘルパー クエリは良いやつだし、カスタム関数はデバッグ（他のクエリのように各ステップの結果を確認可能）できる。

- ヘルパー クエリについて
  - [usdemy @鈴木ひであき -  Excel＆ Power BI両方で使えるPower Query 完全ガイド　初級者から上級者まで対応](https://www.udemy.com/course/power-query-jp/)
  - [YouTube @ExcelドカタCH - この中にヘルパークエリを邪魔者あつかいしている人がいたら手を上げてください【Excel PowerBI】](https://www.youtube.com/watch?v=uLV85sauJRw)
  - [note @Excelドカタの書きもの - なにかと邪魔者あつかいされるヘルパークエリが実は有能すぎた【Excel PowerQuery PowerBI】](https://note.com/excel_dokata/n/nb0e2e575cf39)
- カスタム関数について
  - [learn.microsoft - カスタム関数の使用](https://learn.microsoft.com/ja-jp/power-query/custom-function)

「Power Query カスタム関数」でググって最初にヒットするのはMSのページだし、最初からそのとおりにすれば良かったんだけど、相変わらずMSのページは読みにくいのでちゃんと読んでなかった。

本記事では自分なりに調べたことも加えつつ説明。こういう情報って、知ってる人は知っているんだろうけど、まとまった記事を見つけられなかったので作成してみた。

## 1.3. 先にまとめ
- ヘルパー クエリについて
  - `サンプル ファイルの変換`クエリは`ファイルの変換`関数とリンクしている
  - そのため、`サンプル ファイルの変換`クエリを編集すれば、`サンプル ファイル`以外のファイルにも同じ変換が適用される
- カスタム関数について
  - デバッグ可能なカスタム関数の作成方法
    1. パラメーターを作成: パラメーターを作成し、引数にしたい値（or クエリ）を参照
    2. カスタム関数作成用クエリを作成: そのパラメーターを参照するクエリを作成。カスタム関数で行いたい処理を設定。
    3. カスタム関数を作成: カスタム関数作成用クエリを右クリックし **[関数の作成]** で、このクエリにリンクしたカスタム関数を作成。
  - デバッグ方法: パラメーターにセットする値を変えれば、カスタム関数作成用クエリの各ステップの結果を確認可能
  - カスタム関数の引数
    - 引数は複数設定可能。カスタム関数作成用クエリで複数のパラメーターを参照すればよい。
    - 詳細エディターで直接編集すれば、GUIで設定できない型（テーブル等）も引数にできる。その場合、GUIのパラメーターの管理画面がエラーで閉じれなくなるので注意。ただし、M言語の動作上は何の問題もない(https://bengribaudo.com/blog/2021/03/17/5523/power-query-m-primer-part20-metadata) 、、、と思う。
  - ドキュメント作成: ドキュメントは関数の型のメタデータとして記載する。そのメタデータ付きの型を関数にセットすれば、ドキュメント付きのカスタム関数を作成できる。


# 2. ヘルパー クエリは良いやつという話
ヘルパー クエリとは、GUIでクエリを作成した際にたまに出る、なんかごちゃごちゃしたやつ。↓みたいな感じ。
![ヘルパークエリ出現 - コピー_trimed.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239172/212f92e4-5dd3-e1fc-69ba-edaac7717bed.png)

データを取得するクエリを1つ作るつもりが5つも生成されてしまい、ごちゃごちゃするわ、読むのが面倒だわ、使い方が分からないわ、消せないわで敬遠してしまいがち。そして、ネット上では日本語に限らずこれを回避する方法が紹介されていたりする。

だがしかし、ヘルパー クエリは実は可能性の獣だった。。。

## 2.1. ヘルパー クエリ出現
ユースケース: フォルダー内に含まれる、データ形式が同じ複数のファイルに対し、データを変換しつつ一つのテーブルに結合。

e-statの[漁業・養殖業生産統計年報](https://www.e-stat.go.jp/stat-search/files?page=1&layout=datalist&toukei=00500216&tstat=000001015174&cycle=0&tclass1=000001034726&tclass2val=0)から、海面漁業魚種別漁獲量累年統計（都道府県別）の各県のデータ（.xlx形式😞）を使って説明。.xlxファイルを使うのは本記事説明のためなので、実際にe-statのデータを使う場合はAPIなりを使いましょう（使い方知らんけど）。

まずは以下のファイルをローカルに保存。
- [北海道(昭和31年～平成27年)](https://www.e-stat.go.jp/stat-search/file-download?statInfId=000031605834&fileKind=0)
- [青森(昭和31年～平成27年)](https://www.e-stat.go.jp/stat-search/file-download?statInfId=000031605835&fileKind=0)
- [岩手(昭和31年～平成27年)](https://www.e-stat.go.jp/stat-search/file-download?statInfId=000031605836&fileKind=0)

データの中身はこんな感じ。うーん、このデータ。。。
![元データ_trimed.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239172/78c72789-82f0-39e7-f1dc-7d88fc7c299d.png)


さっそくPower BIに取り込んでみる。Power BIの **[データを取得]** -> **[詳細…]** から **[フォルダー]** を選択:

![データを取得-フォルダー.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239172/da3a3189-372f-807b-99d7-d9aa60b20de9.png)


先ほどのファイルを保存したパスを指定し、**[結合]** のプルダウンから **[データの結合と変換]** を選択:

![フォルダー選択-データの結合.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239172/cdff1ffd-c8ff-e939-6fdf-1a1747a2e92b.png)

サンプル ファイルと取得対象のシート（存在する場合はテーブル）を指定:

![Fileの結合.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239172/d2b40e74-2649-806f-f690-40b90a0b7208.png)

ヘルパー クエリが出現。Power Query エディターがごちゃごちゃする('ω')

![ヘルパークエリ出現 - コピー.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239172/c923bead-7890-21d0-5bc7-204eb542d257.png)

自動的に作成されたクエリはごちゃごちゃで読むのが面倒だし、このケースではデータをまともに変換できていないから、とりあえずデータの取得からやり直してみたくなる。。

## 2.2. `サンプル ファイルの変換`クエリを使ってみる
実は、変換を直すだけなら`サンプル ファイルの変換`クエリを修正するだけでOK。`サンプル ファイルの変換`クエリを詳細エディターで開き、以下を貼り付ける。

```PowerQuery:サンプル ファイルの変換
let
    ソース = Excel.Workbook(パラメーター2, null, true),
    Sheet1 = ソース{[Name="Sheet1"]}[Data],
    都道府県 = Sheet1[Column1]{3},
    不要行削除 = Table.Skip(Sheet1,4),
    不要行削除1 = Table.AlternateRows(不要行削除,3,1,1000000),
    インデックス作成 = Table.FirstN(不要行削除1,3),
    インデックス作成_下方向へコピー済み = Table.FillDown(インデックス作成, Table.ColumnNames(インデックス作成)),
    インデックス作成_転置されたテーブル = Table.Transpose(インデックス作成_下方向へコピー済み),
    インデックス作成_下方向へコピー済み1 = Table.FillDown(インデックス作成_転置されたテーブル,{"Column1", "Column2", "Column3"}),
    インデックス作成_完成 = Table.Transpose(インデックス作成_下方向へコピー済み1),
    値行作成_削除された最初の行1 = Table.Skip(不要行削除,3),
    値行作成_フィルターされた行 = Table.SelectRows(値行作成_削除された最初の行1, each [Column1] <> null and [Column1] <> ""),
    インデックスと値統合 = Table.Combine({インデックス作成_完成, 値行作成_フィルターされた行}),
    転置されたテーブル1 = Table.Transpose(インデックスと値統合),
    変更された型 = Table.TransformColumnTypes(転置されたテーブル1,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    昇格されたヘッダー数 = Table.PromoteHeaders(変更された型, [PromoteAllScalars=true]),
    #"名前が変更された列 " = Table.RenameColumns(昇格されたヘッダー数,{{"年次", "分類1"}, {"年次_1", "分類2"}, {"年次_2", "分類3"}}),
    ピボット解除された他の列 = Table.UnpivotOtherColumns(#"名前が変更された列 ", {"分類3", "分類2", "分類1"}, "年", "漁獲量"),
    漁獲量欠損値をnullに = Table.ReplaceValue(ピボット解除された他の列, each if List.Contains({"… ", "x ", "- "},[漁獲量]) then [漁獲量] else false, null,Replacer.ReplaceValue, {"漁獲量"}),
    漁獲量欠損値をnullに_変更された型1 = Table.TransformColumnTypes(漁獲量欠損値をnullに,{{"漁獲量", Int64.Type}}),
    年変更 = Table.TransformColumns(漁獲量欠損値をnullに_変更された型1, {"年", each Text.Middle(_, Text.PositionOf(_, "(")+1,4)}),
    年変更_変更された型1 = Table.TransformColumnTypes(年変更,{{"年", type number}}),
    都道府県列追加 = Table.AddColumn(年変更_変更された型1, "都道府県", each 都道府県, type text),
    並べ替えられた列 = Table.ReorderColumns(都道府県列追加,{"年", "都道府県", "分類1", "分類2", "分類3", "漁獲量"})
in
    並べ替えられた列
```

すると、`サンプル ファイルの変換`クエリがちゃんと[整然データ](https://id.fnshr.info/2017/01/09/tidy-data-intro/)に変換される。
![サンプル ファイrの変換_trimed.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239172/0f76c655-5528-206c-1015-daf506514770.png)

…だけじゃなく、`source`クエリを見てみると、 **サンプル ファイル以外のファイルにも同じ変換が適用され、テーブルが結合されている！！** ヘルパー クエリ、良いやつじゃん(*^^)



# 3. カスタム関数をデバッグする
実は、ヘルパー クエリはカスタム関数を自動で作る仕組み。なので、上でやったことはカスタム関数のデバッグとも言える。ヘルパー クエリの仕組みを理解してカスタム関数を作成すれば、デバッグできるということ。
## 3.1. ちょっと寄り道
読み飛ばしてもOKだけど、よりPower Queryの理解が深まる。
### 関数の基礎
以下を参考
- [Qiita @spumoni - Mから始めよう #6 〜Power Queryの関数を作成する](https://qiita.com/spumoni/items/6841aec922c02ee92a2f)
- [Ben Gribaudo - Power Query M Primer (Part 2): Functions: Defining](https://bengribaudo.com/blog/2017/11/28/4199/power-query-m-primer-part2-functions-defining)
- [Ben Gribaudo - Power Query M Primer (Part 3): Functions: Function Values, Passing, Returning, Defining Inline, Recursion](https://bengribaudo.com/blog/2017/12/08/4270/power-query-m-primer-part3-functions-function-values-passing-returning-defining-inline-recursion)


例えば、ふたつの数値`a`と`b`の合計の2倍を返す関数を定義するには以下：
```PowerQuery:doubleSum
(a as number, b as number) as number =>
let
    ret = 2 * (a + b)
in
    ret
```

簡単な説明：
- 最初の括弧: 引数とその型（型は指定しなくても可）
- その括弧のすぐ後: 戻り値の型
- =>以降: 戻り値

空のクエリ「doubleSum」を作成し、詳細エディターから上記を貼り付ければ、Power Query内でカスタム関数として使用可能になる。関数名はクエリ名。
![カスタム関数例_trimed.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239172/c59fd633-d42b-8850-775f-0a1a0d4a2725.png)


### パラメーターとメタデータの基礎
以下を参考：
- [Ben Gribaudo - Power Query M Primer (Part 20): Metadata](https://bengribaudo.com/blog/2021/03/17/5523/power-query-m-primer-part20-metadata)
- [Qiita 
@tanuki_phoenix - Metadataをたまには使ってみる](https://qiita.com/tanuki_phoenix/items/00b985f4440b544791fb)

パラメーターは値に特殊なメタデータがセットされたクエリ。試しに空のクエリを作成し、詳細エディターで以下とする：
```M
"hoge" meta [IsParameterQuery=true, Type="Number"]
```
すると、[パラメーターの管理]にこのクエリが追加される。

`Type=`の右辺に設定可能な（ホスト環境が認識する）型は以下。カスタム関数で使える型と同じかと思いきや、TableやListが含まれていない（なぜだ？？）。
|型|値|
|:-|:-|
|すべて|"Any"|
|10進数|"Number"|
|日付/時刻|"DateTime"|
|日付|"Date"|
|時刻|"Time"|
|日付/時刻/タイムゾーン|"DateTimeZone"|
|期間|"Duration"|
|テキスト|"Text"|
|True/False|"Logical"|
|バイナリ|"Binary"|

メタデータはM言語側では意味がないものではあるが、ホスト環境（Power BIやExcel）側から利用されるもの。パラメーターの管理がその例。パラメーターに限らず、ホスト環境側はM言語の内容に応じて表示を変えたりする。「ナビゲーション」ステップのように。

## 3.2. パラメーターを使用してカスタム関数を作成する
[learn.microsoft - カスタム関数の使用](https://learn.microsoft.com/ja-jp/power-query/custom-function)の内容から少しはみ出つつ、カスタム関数を作成しよう。

データ ソースは先ほどと同様、e-statのデータを使用する。新しい.pbixファイルを作成し、クエリ エディターを開く。以下の4つのクエリを作成（**[空のクエリ]** から詳細エディターで直接入力）。各クエリとも **[読み込みを有効にする]** のチェックを外しておく。

```PowerQuery:ファイル一覧
let
    folderPath = "path to folder", //適宜書き換え
    ソース = Folder.Contents(folderPath),
    WorkBook列追加 = Table.AddColumn(ソース, "Workbook", each Excel.Workbook([Content]), type table)
in
    WorkBook列追加
```

```PowerQuery:sampleWorkbook
let
    fileName = "c001c-005-002-000-000.xls",
    ソース = ファイル一覧{[Name=fileName]}[Workbook]
in
    ソース
```

```PowerQuery:argWorkbook
sampleWorkbook meta [IsParameterQuery=true, Type="Table", IsParameterQueryRequired=true]
```

```PowerQuery:データ変換関数作成用
let
    ソース = argWorkbook,
    Sheet1 = ソース{[Name="Sheet1"]}[Data],
    都道府県 = Sheet1[Column1]{3},
    不要行削除 = Table.Skip(Sheet1,4),
    不要行削除1 = Table.AlternateRows(不要行削除,3,1,1000000),
    インデックス作成 = Table.FirstN(不要行削除1,3),
    インデックス作成_下方向へコピー済み = Table.FillDown(インデックス作成, Table.ColumnNames(インデックス作成)),
    インデックス作成_転置されたテーブル = Table.Transpose(インデックス作成_下方向へコピー済み),
    インデックス作成_下方向へコピー済み1 = Table.FillDown(インデックス作成_転置されたテーブル,{"Column1", "Column2", "Column3"}),
    インデックス作成_完成 = Table.Transpose(インデックス作成_下方向へコピー済み1),
    値行作成_削除された最初の行1 = Table.Skip(不要行削除,3),
    値行作成_フィルターされた行 = Table.SelectRows(値行作成_削除された最初の行1, each [Column1] <> null and [Column1] <> ""),
    インデックスと値統合 = Table.Combine({インデックス作成_完成, 値行作成_フィルターされた行}),
    転置されたテーブル1 = Table.Transpose(インデックスと値統合),
    変更された型 = Table.TransformColumnTypes(転置されたテーブル1,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    昇格されたヘッダー数 = Table.PromoteHeaders(変更された型, [PromoteAllScalars=true]),
    #"名前が変更された列 " = Table.RenameColumns(昇格されたヘッダー数,{{"年次", "分類1"}, {"年次_1", "分類2"}, {"年次_2", "分類3"}}),
    ピボット解除された他の列 = Table.UnpivotOtherColumns(#"名前が変更された列 ", {"分類3", "分類2", "分類1"}, "年", "漁獲量"),
    漁獲量欠損値をnullに = Table.ReplaceValue(ピボット解除された他の列, each if List.Contains({"… ", "x ", "- "},[漁獲量]) then [漁獲量] else false, null,Replacer.ReplaceValue, {"漁獲量"}),
    漁獲量欠損値をnullに_変更された型1 = Table.TransformColumnTypes(漁獲量欠損値をnullに,{{"漁獲量", Int64.Type}}),
    年変更 = Table.TransformColumns(漁獲量欠損値をnullに_変更された型1, {"年", each Text.Middle(_, Text.PositionOf(_, "(")+1,4)}),
    年変更_変更された型1 = Table.TransformColumnTypes(年変更,{{"年", type number}}),
    都道府県列追加 = Table.AddColumn(年変更_変更された型1, "都道府県", each 都道府県, type text),
    並べ替えられた列 = Table.ReorderColumns(都道府県列追加,{"年", "都道府県", "分類1", "分類2", "分類3", "漁獲量"})
in
    並べ替えられた列
```

4つ目は先ほど作成した`サンプル ファイルの変換`クエリの1行目を変えただけ。

`データ変換関数作成用`クエリを右クリックし、**[関数の作成]** をクリック:
![関数の作成_trimed.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239172/47c9daa9-88ae-af5b-0dc4-7c36f0d5c6d3.png)

関数名は`データ変換関数`とする。
![関数の作成ダイアログ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239172/c90f9b59-7616-d83e-80bf-3dd5bfabe35c.png)

カスタム関数を作成すると、いくつかのクエリが自動的にグループ化される。
![カスタム関数作成直後_trimed.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239172/e8d62098-a0a8-7eb4-de0f-276feb820b97.png)


:::note warn
「つまり、ヘルパー クエリは……**これら一連の流れを自動生成する仕組み**だったんだよ！！」
（バーーーン！！）
Ω ΩΩ＜「**な……なんだってーーー！！**」
:::

作成した`データ変換関数`カスタム関数を使ってみる。「以下のクエリを追加し、この関数を呼び出してデータを結合。
```PowerQuery:F_漁獲量
let
    ソース = ファイル一覧,
    データ変換関数呼び出し = Table.AddColumn(ソース, "変換後", each データ変換関数([Workbook]), type table),
    テーブル結合 = Table.Combine(データ変換関数呼び出し[変換後])
in
    テーブル結合
```

すると、ヘルパー クエリの場合と同じ結果が得られる。

なお、クエリのグループ化を使用して見た目を整えておくと後の人（明日の自分を含む）が見やすい：

![グループ化して整える_trimed.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239172/cfdcd386-1762-678f-8fb1-75325350e628.png)


## 3.3. カスタム関数のデバッグ
`データ変換関数`カスタム関数は`データ変換関数作成用`クエリとリンクしているため、各ステップを確認することでデバッグ可能。

対象のExcelファイルを変更するには、`sampleWorkbook`クエリの参照先を、デバッグしたいExcelファイルに変更する。その上で、`データ変換関数作成用`クエリの各ステップを確認。あるファイルでは上手く行ったけど、他のファイルではダメだったというのはよくある話。

サンプル ファイルを北海道のファイルから青森のファイルに変更してみよう。`sampleWorkbook`クエリを以下のように変更する: 

```PowerQuery:sampleWorkbook
let
    fileName = "c001c-005-003-000-000.xls",
    ソース = ファイル一覧{[Name=fileName]}[Workbook]
in
    ソース
```

すると、`データ変換関数作成用`クエリの結果も青森のものに変更される: 
![サンプル ファイrの変換_青森_trimed.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239172/5e113ee6-0bbb-1ebe-41cf-cc4b9b1c3b88.png)

この例では問題なく変換できているが、上手く行っていない場合は`データ変換関数作成用`クエリの各ステップを確認すること。

## 3.4. 注意
上記で設定した`argWorkbook`パラメーターのせいで、 **[パラメーターの管理]** 画面でエラーが表示され、**[OK]** ボタンで閉じられなくなる。これはM言語のホスト環境（Power BI）側がパラメーターの値としてテーブルを想定していないため。M言語側には問題ない、たぶん（関数の作成はホスト環境が提供しているが。。）。気になるなら、`sampleWorkbook`クエリの参照先をバイナリー ファイルに変更する（併せて、`データ変換関数作成用`クエリの2行目を `ソース = Excel.Workbook(argWorkbook)` に変更する）。


## 3.5. 引数を2つ以上にする
パラメーター クエリを新しく作成し、カスタム関数の元になったクエリで参照する。引数の型の設定方法はメタデータの説明で述べたとおり。

例えば、フォルダー内に含まれる、データ形式が同じ複数のファイルに対し、ファイル内の複数のテーブルを取得し、それぞれ1つのテーブルに結合したい場合。このケースでは、テーブルの取得処理自体は共通なので、ワークブック、テーブル名、データの型変換の3つを引数とするカスタム関数の利用が考えられる。

（この記事、だいぶ長くなっちゃって書くのが疲れてきた。でも、もうちょっと、、、がんばってみるか。。。。）

:::note warn
明日からがんばるんじゃない・・・・・・
今日・・・
今日**だけ**がんばるんだっ・・・・・・！

今日をがんばった者・・・
今日をがんばり始めた者にのみ・・・

明日が来るんだよ・・・！

――― 福本伸行『賭博破戒録カイジ』（講談社）より、大槻班長
:::

以下のデータを含むExcelファイルを作成。データはちゃんと[テーブル](https://qiita.com/k_maki/items/b6086a9fb1de5ecb1da3)にしてね。ファイルは3つ作成し、ファイル名は`sample1.xlsx`、`sample2.xlsx`、`sample3.xlsx`とする。

テーブル名: `T.Table1`
|A|B|C|
|-:|:-|-:|
|1|a|1:00|
|2|b|2:00|
|3|c|3:00|

テーブル名: `T.Table2`
|D|E|F|
|:-|-:|-:|
|イ|1,000|2022/12/1|
|ロ|2,000|2022/12/2|
|ハ|3,000|2022/12/3|

新しい.pbixファイルを作成し、クエリエディターを開く。以下の8つのクエリを作成（**[空のクエリ]** から詳細エディターで直接入力）。各クエリとも **[読み込みを有効にする]** のチェックを外しておく。

```PowerQuery:ファイル一覧
let
    folderPath = "path to folder", //適宜書き換え
    ソース = Folder.Contents(folderPath),
    WorkBook列追加 = Table.AddColumn(ソース, "Workbook", each Excel.Workbook([Content]), type table)
in
    WorkBook列追加
```

```PowerQuery:sampleWorkbook
let
    fileName = "sample1.xlsx",
    ソース = ファイル一覧{[Name=fileName]}[Workbook]
in
    ソース
```

```PowerQuery:sampleTableName
"T.Table1"
```

```PowerQuery:sampleTypeTransformation
{{"A", Int64.Type}, {"B", type text}, {"C", type time}}
```

```PowerQuery:argWorkbook
sampleWorkbook meta [IsParameterQuery=true, Type="Table", IsParameterQueryRequired=true]
```


```PowerQuery:argTableName
sampleTableName meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]
```

```PowerQuery:argTypeTransformation
sampleTypeTransformation meta [IsParameterQuery=true, Type="List", IsParameterQueryRequired=true]
```

```PowerQuery:データ変換関数作成用
let
    ソース = argWorkbook,
    T_Table = ソース{[Item=argTableName, Kind="Table"]}[Data],
    変更された型 = Table.TransformColumnTypes(T_Table, argTypeTransformation)
in
    変更された型
```

`データ変換関数作成用`クエリを右クリックし、**[関数の作成]** をクリック。関数名は`データ変換関数`とする。

作成した`データ変換関数`カスタム関数を利用して、`T.Table1`テーブル、`T.Table2`テーブルからデータを取得し結合するには、以下のクエリで。

```PowerQuery:F_Table1
let
    tableName = "T.Table1",
    typeTransformation = {{"A", Int64.Type}, {"B", type text}, {"C", type time}},
    ソース = ファイル一覧,
    データ変換関数呼び出し = Table.AddColumn(ソース, "Table", each データ変換関数([Workbook], tableName, typeTransformation)),
    テーブル結合 = Table.Combine(データ変換関数呼び出し[Table])
in
    テーブル結合
```

```PowerQuery:F_Table2
let
    tableName = "T.Table2",
    typeTransformation = {{"D", type text}, {"E", type number}, {"F", type date}},
    ソース = ファイル一覧,
    データ変換関数呼び出し = Table.AddColumn(ソース, "Table", each データ変換関数([Workbook], tableName, typeTransformation)),
    テーブル結合 = Table.Combine(データ変換関数呼び出し[Table])
in
    テーブル結合
```

パラメーター化しているため、両クエリの違いは `tableName` と `typeTransformation` のみ。

結果はこんな感じになる。グループ化でクエリを整えておくことを忘れずに。
![複数のパラメーター_trimed.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239172/e26c1c9c-d1ea-708a-5ae8-ac3b8f797db2.png)

## 3.6. ドキュメントを追加する
せっかく覚えたので、ドキュメントの追加方法も簡単に説明。後日、詳しく調べて別記事にするかもしれないし、しないかもしれない。
- [learn.microsoft - 関数ドキュメントの追加](https://learn.microsoft.com/ja-jp/power-query/handlingdocumentation)
- [Ben Gribaudo - Power Query M Primer (Part 20): Metadata](https://bengribaudo.com/blog/2021/03/17/5523/power-query-m-primer-part20-metadata)

先ほどの例（引数を2つ以上にする）で、`データ変換関数`を作成したが、以下の画面のとおり、説明が何にも無い。
![カスタム関数ドキュメント無し_trimed.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239172/edffeeb7-3b1c-fd1f-3d92-7664282896f9.png)

その一方、標準関数の場合は詳しいドキュメントが表示される。
![Number.Sqrt関数のドキュメント_trimed.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239172/977c05bc-24c6-38e2-b536-2b6fed6754f0.png)

手間はかかるが、説明が何もないと、他の人は使いづらいし、時間が経つと自分でも忘れてしまうのでドキュメントを追加してみよう。以下のクエリを追加すればOK。

```PowerQuery:データ変換関数_doc付
let
  Type = type function (
    argWorkbook as (type table meta 
      [
        Documentation.FieldCaption = "workbook",
        Documentation.SampleValues = {""}
      ]
    ), 
    argTableName as (type text meta[
        Documentation.FieldCaption = "tableName",
        Documentation.SampleValues = {"Excelのテーブル名"}
    ]), 
    argTypeTransformation as (type list meta[
        Documentation.FieldCaption = "typeTransformation",
        Documentation.SampleValues = {}
    ]))
    as table meta [
      Documentation.Name = "データ変換関数_doc付", 
      Documentation.LongDescription = 
        "ワークブックからテーブルを取得し型変換を行う関数。" &
        "<ul>" &
          "<li>" &
            "<code>workbook</code> (table) : Excel.Workbookの戻り値。" &
          "</li>" &
          "<li>" &
            "<code>tableName</code> (text) : 取得対象とするテーブルの名前。" &
          "</li>" &
          "<li>" &
            "<code>typeTransformation</code> (list) : 取得したテーブルに対して適用するデータ型変換。<code>TransformColumnTypes</code>の第2引数。" &
          "</li>" &
        "</ul>",
      Documentation.Examples = {
        [
            Description = "例のタイトル", 
            Code = "データ変換関数_doc付(Excel.Workbook(File.Content(""path to xlfile""), ""T.Table1"", {{""A"", type number}, {""B"", type text}})", 
            Result = "⇒と同じもの。Table.TransformColumnTypes(Excel.Workbook(File.Contents(""path to xlfile"")){[Item=""T.Table1"",Kind=""Table""]}[Data],{{""A"", type number}, {""B"", type text}})"],
        [Description = "例はいくつでも書けるよ", Code = "風が語り掛けます", Result = "うまい、うますぎる！"]
        }
    ],
  RetypedFunction = Value.ReplaceType(データ変換関数, Type)
in
  RetypedFunction
```

すると、次のようにドキュメントが追加されてわかりやすくなる。
![カスタム関数ドキュメントあり_trimed.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239172/b0fabce4-bf79-37e2-a4bd-f1fcd6c4caf9.png)


よくわかっていないので詳しく説明はしないが、ポイントはドキュメントは関数のメタデータではなく、関数の型のメタデータから生成されるということ。そのため、メタデータ付きの関数の型を定義して、カスタム関数の型をその型で置き換えるという2ステップが必要になる。

あと、`Documentation.LongDescription`で関数の詳細を記述するが、この部分はHTMLタグが使える。なので、この例では`<UL>`タグや`<LI>`タグを使用している。太字にするには`<B>`、改行して複数行にするには`<BR>`を使えばOK。あまり試していないが、他にもいろんなタグが使えそう。

ドキュメント作成時には、標準関数のドキュメント記載方法が参考になる。例えば、`Sql.Database`のドキュメントを参考にするには、次のように`Value.Metadata(Value.Type())`でメタデータを取得し、各要素を調べればよい。関数を変えるには、`func =`の右辺を変更する。

```PowerQuery:関数のドキュメント取得
let
    func = Sql.Database,
    metaData = Value.Metadata(Value.Type(func))
in
    metaData
```
![関数のドキュメント取得_trimed.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/239172/397e4f9e-353a-7a99-480b-08b65bf01d24.png)


本編は以上。こんなニッチな記事を最後まで読んでくれてありがとうございます。がんばって書いたので、いいね👍してくれるとうれしいです。

# 4. おわりに
実はこの記事は
[Microsoft Power BI Advent Calendar 2022](https://qiita.com/advent-calendar/2022/powerbi)の23日目のものでした（今更）。年末らしく、今年の振り返りと来年やりたいことで〆よう。

## 4.1. 今年の振り返り
[去年末にDAXを理解できて](https://qiita.com/k_maki/items/08a017d8d480cde28b46)から、Power BIのデータ モデルをどう作るべきか、何をやってはいけないかが一気にわかった（気がする）。データ モデリングをする上で、DAXメジャーの理解は重要、超重要。今年は、Power Queryで[List.Generateを使った高度な処理](https://qiita.com/tanuki_phoenix/items/3a9ce35180949150e263)もできるようになったし、カスタム関数のデバッグもできるようになった（関係ないけど手縫いでズボンの裾上げもできるようになった）。セルフサービスBIについてはあとは数をこなすだけかな。

## 4.2. 来年やりたいこと
来年はエンタープライズBIを何とかしたい。組織としてBIの恩恵を受けるにはここをしっかりしないと。Power BIが最適な手段かはまだ分からないけど、エンタープライズBIを導入できるよう、データ基盤の構築方法や情報管理の方法について、まずは勉強から始めたい。

その他、チームとしてのBI開発方法・ルールや、組織内開発者養成、先延ばしになっているDAXクエリの勉強とかに取り組んでみたいところ。

果たして組織にBIを根付かせることができるか。。来年のがんばり次第か。。。('A`)