Power BI レポートを改善しよう！～カスタム関数で複数のブック・シートのExcelファイルを一括読み込み～

１．はじめに

Power BI でレポートを作成していて、こんな事に悩んだりすることはありませんか？

手動で作成しているExcelファイルを読み込みたいけど、Excelのデータがテーブルの形になっていないためBI上で集計できない
複数のシートのデータをまとめて読み込みたい
複数のブックのデータをまとめて読み込みたい

と言うのも、上記は筆者が実際に直面したことがある問題だったりします。

日々の業務での作業時間をExcelに記録したものがあるのですが、これをPower BI に読み込ませて可視化していました。
ところがこのExcelファイル、手運用しているものだったので、以下のような作りになっています。

シートは区分と日付のマトリックスになっており、シートごとに読み込むべき列数が異なる
シートごとにひと月分のデータを記録している
ブックごとに１年分のデータを記録している

１つ１つのシートにそれぞれ加工を加えて読み込む必要はあるけれど、加工を加えた後は同じテーブルに結合したい、という状態です。

最初はシートごとに手動で読み込んで成形し、最終的に全てのクエリを結合していました。これでも実現は出来ますが、作業するたびにクエリが増えていきます。
また、シートが増えるごとに手作業が必要になるため、忘れるとデータが更新されないままのレポートが放置されることになります。これは良くないですよね。
シートごとの成形作業は、行数や列数などが多少異なるのみで、毎月ほぼ同じクエリを流用しています。これはどうにかして自動化したい……。

そんな時には自動化したい部分をカスタム関数にしてあげると、課題が解決できるかもしれません。
今回はそんな活用案をご紹介します。

おおまかな流れは以下の通りです。

①読み込みたいデータ（シート単位）を一覧化する
②シート単位で成形処理を行うカスタム関数を作成する
③作成したカスタム関数を①の列から呼び出す
④不要な列を削除して完成

２．Power Queryのカスタム関数とは

そもそもカスタム関数とは何なのか？
簡単に言うと、Power Queryで作成できる「自作の関数」です。そのまんまですね。
難しく感じるかもしれませんが、マウス操作でExcelファイルやフォルダの読み込み等を行ったときに自動で作成されるものと同じです。

一度作成しておくと再利用が出来るため、よく行う処理をカスタム関数にしてまとめておくと、便利になることがあります。
それではやってみましょう。

３．読み込むデータを展開する

今回はシート単位でカスタム関数を呼び出す想定なので、まずは取り込んだデータをシート単位で一覧化します。
不要なシートはフィルタ条件などを指定して、除外しておきます。

４．カスタム関数を作成する

新しいクエリ＞空白のクエリ　から、新しいクエリを作成します。
作成したクエリを右クリック>関数の作成でも関数を作成できますが、詳細エディターから関数の中身を入力すると自動で関数として認識されるので、どちらでも構いません。

カスタム関数となるクエリを作成したら、不要な列の削除やピポット解除・列の変更等、今までシートごとに手動で行っていた成形作業を関数内で行っていきます。
具体的な記述内容は元データの形式や行いたい処理で変わるため割愛しますが、ポイントとしては、シートごとに値が変わる部分は直接指定しないように作成します。
シート本体のデータや月ごとに値が変わる日数などの部分については、引数で受け取れるようにします。

慣れるまでは先に手作業で成形作業を行っておき、クエリをコピペして必要な箇所だけ修正していくと、いきなりクエリを書くよりも楽だと思います。

５．カスタム関数の呼び出し

カスタム関数を作成したらテーブルのクエリに戻り、列の追加>カスタム関数の呼び出しで呼び出します。

ダイアログが表示されますので、呼び出す関数やパラメーターの指定を行います。
パラメーターは作成した関数で指定しているパラメーターがある場合に表示されます。呼び出し元の列の値か、任意の固定値を指定することが可能です。

「OK」を選択すると、結果が「新しい列名」で指定した列に追加されます。

今回の場合は他の列は不要なので削除し、カスタム関数の結果列のテーブルを展開します。

シートごとのテーブルが展開され、複数ブック・複数シートのExcelファイルをそれぞれ成形したあと、一つのテーブルとして読み込むことができました。
※画面には見えていませんが、全てのデータが一つのテーブルに結合されています

あとは普段通り必要なビジュアルに追加してレポートを作成すれば、複数ブック・シートのExcelファイルのデータを一つのレポートで集計できます。

６．最後に

いかがだったでしょうか。
今回はPower BI側の処理のみで全て解決しましたが、プログラム等で成形処理を行った後一度DBにデータを格納し、DBのデータをBIで参照する方法もあります。Power Queryのみで処理するよりも大掛かりにはなりますが、より柔軟な対応が出来ますので、用途に応じて検討していただければと思います。

QESでは、Microsoft製品やAWS製品に関するソリューションに取り組んでおります。
他プロダクトに関するブログも投稿しておりますので、下記のリンクから是非ご覧ください。

ブログを読んで弊社の業務内容に興味を持っていただけましたら、採用情報にもお目通しいただければ幸いです。
https://www.qes.co.jp/recruit.htm

PowerPlatformの導入やPowerAppsによるアプリケーション開発も行っていますので、お困りの際はぜひお問い合わせください。

※このブログで参照されている、Microsoft、Windows、Azure、SharePoint、PowerAppsその他のマイクロソフト製品およびサービスは、米国およびその他の国におけるマイクロソフトの商標または登録商標です。