Power Query でクエリを関数化する方法

# 公開データからレポートを作成
日本政府観光局（JNTO）の公開データは Excel ファイル内で**年ごとにシートが分かれている**パターンです。Power BI でレポートを作る方法はこちらで公開されています。

https://qiita.com/mimocihz/items/e7a4742c099691caa33b

ここではやっていませんが、URL をパラメーターにしておけば、毎月変更されるファイル名に対応しやすくなります。

:::note info
パラメーター化しておくと Power BI サービスに発行した後、セマンティック モデル (データセット) の設定ページでパラメーター値を変更できるようになります。
つまり、Power BI Desktop で修正して再発行しなくて良いのです。
:::

一般社団法人　日本自動車販売協会連合会（略称　自販連）のサイトに、[メーカー別の新車販売台数が公開](http://www.jada.or.jp/data/month/m-r-hanbai/m-r-maker/) されているので、この記事ではその Excel から5年分のデータを取得して Power BI のレポートを作ります。

http://www.jada.or.jp/data/month/m-r-hanbai/m-r-maker/

## データが細切れに公開されている問題
ところが、年月ごとにファイルが分かれて公開されているために (12か月*5年で) 60ものファイルを取り込まなければなりません…。
今まで頭を使わず手を使って対処していました。つまり、クエリをコピペして、年月を書き換えて (列名変えるところはパラメーター使う処理入れるの面倒なので手作業) 追加 (UNION) していたのです。が、クエリをコピペして年月を書き換えるってイマイチだなーと思っていました。パラメーターで最初と最後の年月を指定して、この処理をぐるぐる回したいのです。

## 繰り返し処理
クエリ内での繰り返し処理については、検索すれば色々情報が出てきます。でも、クエリそのものを繰り返したいときはどうしたらいいのか…と試行錯誤していたら見つけました。クエリを関数にすればいいのです。

### クエリを関数に変換するには
クエリを右クリックすると [関数の作成] というメニューがあります。なので繰り返したいクエリに対して [関数を作成] すればいいということです。ただし、関数というからには引数＝パラメーターが必要です。今回はソースとなる Excel ファイルのパスをパラメーターにします。

### 関数のベースとなるクエリの作り方
Web コネクタを使用して下記の Excel ファイルパスを入力し、[データの変換] して Power Query エディターを開きます。
`
http://www.jada.or.jp/contents/data/maker/excel/maker202301.xls
`

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2668909/41eb3550-6c68-75d4-e36d-4a6890b9e9d3.png)

ほしいデータは21行目以降なので、[行の削除] で20行削除し、[1行目をヘッダーとして使用] します。
さらにほしい列は2列目と3列目なので、「メーカー」列と「当月(A)」列を選択して [他の列の削除] します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2668909/8abe9a06-220e-6e50-da3a-81ad67d51871.png)

「当月(A)」列の中身が「null」の行を削除します。さらに「メーカー」列の中身が「合計」の行を削除します。
メーカー名にスペース (半角と全角) が含まれているので、[値の置換] で消していきます。
列名も次のように変更します。
- 「メ   ー   カ   ー」⇒「メーカー」
- 「当月(A)」⇒「台数」

出来上がりはこのようになります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2668909/52877b0f-c34e-c882-7bca-2f6f221b71a4.png)

### ソースをパラメーター化
ソースとなる Excel ファイルのパスを再掲するとこのようになっているので、パラメーター化していきます。
`
http://www.jada.or.jp/contents/data/maker/excel/maker202301.xls
`

| パーツ | 中身 | 作るもの |
|:-:|:-:|:-:|
| 可変部分 | パスの前半。[http://www.jada.or.jp/contents/data/maker/excel/maker] 部分 | テキスト型のパラメーター「ExcelPath」 |
| 可変部分 | yyyymm [202301] の部分 | テキスト型のパラメーター「yyyymm」 |
| 固定部分 | .xls | なし |

先に2つのパラメーターを作成します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2668909/56135a30-07e5-cbf4-fef4-c368c3afce8e.png)

クエリ「Sheet1」に戻り、右側の [適用したステップ] から [ソース] の歯車をクリックします。
[詳細設定] を選択し、URL 部分を作成した2つのパラメーターとテキストの結合となるように定義します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2668909/0d28e818-8bad-3a95-eaa9-fa2477acfc62.png)
クエリがエラーとならないことを確認します。

## クエリを関数に変換
「Sheet1」のクエリを右クリックし [関数の作成] します。関数名に分かりやすい名前 (メーカー別月別新車販売台数) を付けます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2668909/576be4b2-7e24-d8bc-76df-7d9d2a90da65.png)

これで2つのテキストデータをインプットして、テーブルを返す関数ができました！

# まとめ
この記事のタイトルである「Power Query でクエリを関数化する」方法は説明できたかと思います。
[次の記事](https://qiita.com/ayumito/items/4ad480a102c609141424)では、ここで作成した関数の2つ目のパラメーター「yyyymm」部分に「201901」から「202312」を渡して6年分のメーカー別月別新車販売台数を1つの表にする部分を書きます。

https://qiita.com/ayumito/items/4ad480a102c609141424