公開データからレポートを作成
日本政府観光局（JNTO）の公開データは Excel ファイル内で年ごとにシートが分かれているパターンです。Power BI でレポートを作る方法はこちらで公開されています。
ここではやっていませんが、URL をパラメーターにしておけば、毎月変更されるファイル名に対応しやすくなります。
パラメーター化しておくと Power BI サービスに発行した後、セマンティック モデル (データセット) の設定ページでパラメーター値を変更できるようになります。
つまり、Power BI Desktop で修正して再発行しなくて良いのです。
一般社団法人 日本自動車販売協会連合会（略称 自販連）のサイトに、メーカー別の新車販売台数が公開 されているので、この記事ではその Excel から5年分のデータを取得して Power BI のレポートを作ります。
データが細切れに公開されている問題
ところが、年月ごとにファイルが分かれて公開されているために (12か月*5年で) 60ものファイルを取り込まなければなりません…。
今まで頭を使わず手を使って対処していました。つまり、クエリをコピペして、年月を書き換えて (列名変えるところはパラメーター使う処理入れるの面倒なので手作業) 追加 (UNION) していたのです。が、クエリをコピペして年月を書き換えるってイマイチだなーと思っていました。パラメーターで最初と最後の年月を指定して、この処理をぐるぐる回したいのです。
繰り返し処理
クエリ内での繰り返し処理については、検索すれば色々情報が出てきます。でも、クエリそのものを繰り返したいときはどうしたらいいのか…と試行錯誤していたら見つけました。クエリを関数にすればいいのです。
クエリを関数に変換するには
クエリを右クリックすると [関数の作成] というメニューがあります。なので繰り返したいクエリに対して [関数を作成] すればいいということです。ただし、関数というからには引数＝パラメーターが必要です。今回はソースとなる Excel ファイルのパスをパラメーターにします。
関数のベースとなるクエリの作り方
Web コネクタを使用して下記の Excel ファイルパスを入力し、[データの変換] して Power Query エディターを開きます。
http://www.jada.or.jp/contents/data/maker/excel/maker202301.xls
ほしいデータは21行目以降なので、[行の削除] で20行削除し、[1行目をヘッダーとして使用] します。
さらにほしい列は2列目と3列目なので、「メーカー」列と「当月(A)」列を選択して [他の列の削除] します。
「当月(A)」列の中身が「null」の行を削除します。さらに「メーカー」列の中身が「合計」の行を削除します。
メーカー名にスペース (半角と全角) が含まれているので、[値の置換] で消していきます。
列名も次のように変更します。
- 「メ ー カ ー」⇒「メーカー」
- 「当月(A)」⇒「台数」
出来上がりはこのようになります。
ソースをパラメーター化
ソースとなる 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つのパラメーターを作成します。
クエリ「Sheet1」に戻り、右側の [適用したステップ] から [ソース] の歯車をクリックします。
[詳細設定] を選択し、URL 部分を作成した2つのパラメーターとテキストの結合となるように定義します。
クエリがエラーとならないことを確認します。
クエリを関数に変換
「Sheet1」のクエリを右クリックし [関数の作成] します。関数名に分かりやすい名前 (メーカー別月別新車販売台数) を付けます。
これで2つのテキストデータをインプットして、テーブルを返す関数ができました！
まとめ
この記事のタイトルである「Power Query でクエリを関数化する」方法は説明できたかと思います。
次の記事では、ここで作成した関数の2つ目のパラメーター「yyyymm」部分に「201901」から「202312」を渡して6年分のメーカー別月別新車販売台数を1つの表にする部分を書きます。