寒いですね。
ブーツタイプのルームシューズのあたたかさと脱ぎづらさの間で日々煩悶している、DX-HR推進室の堀越です。
本記事は「NEXTSCAPE Advent Calender 2021」の18日目です。
Power BI のフォルダーコネクタは便利
Power BI ではローカルフォルダーやSharePointフォルダーをデータソースに指定することができます。
同じ構造ならば、追加データの入ったファイルをフォルダに追加していけるので、便利に使っています。
こちらのQiita記事で丁寧に解説してくださっています。
SharePointフォルダーからデータを取得してみましょう。
SharePoint フォルダーのコネクタはサイトのコンテンツをすべて読みこんでくれるのでフィルターが必要です。
今回はFolder Pathでフィルターしました。
目的のファイルのみを絞り込むことができたら、Content列のここ(「ファイルを結合」ボタン)を押すと…
こんなウィンドウが出て
OKをクリックすると、フォルダ内のファイルがまとめて1つのクエリとして開かれます。
うん、便利ね。
「ファイルの結合」を使うとき、何が起きているのか？
ところでContent列で「Fileの結合」をクリックしたあとに、ステップがいくつか増えています。
作った覚えのないクエリも作成されています。
Docmentを見てみます。
ふむ！わかりません。
わからなくてもレポートは作れますが、この記事では一つずつ見ていきます。
1. 自動的に作成されるステップ
①フィルター選択された非表示のFile
隠しファイルを除外してくれているようです。
Attributes列には各行Recordが入っていますが、これを展開した際のHiddenフィールドがtrueでない行をフィルターしています。
クエスチョンマークは列が存在しなくてもエラーにせずnullを返します。
SharePointフォルダをデータソースにすると、ここのRecordには
Size、Content Type、Kind の3つしか格納されていませんが、クエスチョンマークのおかげでエラーになりません。
ローカルフォルダをデータソースとしたとき、隠しファイルのAttributes列のRecordを展開すると、こんな感じになっていました。
Recordを展開しなくても、中身のフィールドをフィルター条件にできるんですね。
ちなみにここのステップの歯車マークをクリックすると、以下のウィンドウが出てきます。
謎しかなく、この記事を書くために調べるまでは何をしているかなんとなくしかわかっていませんでした。
②カスタム関数の呼び出し
「ファイルの変換」という列が追加されて、各行にTableが格納されています。
Table.AddColumn(table as table, newColumnName as text, columnGenerator as function, optional columnType as nullable type) as table
Table.AddColumn を使って、
1つ目の引数で、列追加する対象のテーブル名(直前のステップ名)、
2つ目で、追加する列の名称("ファイルの変換"列)、
3つ目で、列の中身を作成するためのカスタム関数を指定しています。
ここはあとで探るとして、先に行きましょう
③名前が変更された列
見たままですね、わかりやすく"Source"を付けてくれています。
④削除された他の列
ファイル名もいらないなーってときは、「ファイルの変換」列だけを残すように編集してしまいましょう。
⑤展開されたテーブル列
「ファイルの変換」列に格納されていた各行のTableが展開されて、いよいよファイルの中身がすべて見られるようになりました。
Table.ExpandTableColumn を使って、各行に格納されているTableを展開しています。
Table.ExpandTableColumn(table as table, column as text, columnNames as list, optional newColumnNames as nullable list) as table
1つ目の引数で、処理する対象のテーブル名、
2つ目でさらに列名、
3つ目で展開した後の列名をリストで渡します。Table.ColumnNames(ファイルの変換(#"サンプル ファイル")はサンプルファイルから列名をリストで取得しています。
⑥変更された型
すべての列に対して、データ型の検出と変更を行っています。
これで自動的に追加されたステップは終わりです。
2. 自動的に作成されるクエリ
次はクエリを見ていきます。
「クエリ1からファイルを変換する」フォルダに、「ヘルパークエリ」フォルダと、「サンプルファイルの変換」クエリが入っています。
「ファイル」「変換」が多すぎてこんがらがるので、クエリ名を変えました。
①カスタム関数「ファイルの変換」
パラメーターで指定したバイナリファイルをExcelとして開き、
financialsテーブルを取得して返却しています。
ここでもう一度、このカスタム変数を呼び出している側を見てみます。
each #"ファイルの変換(カスタム関数)"([Content])
ここや、「カスタム関数の呼び出し」ウィンドウでもわかるように、
Content列のBinaryをパラメーターとして渡し、Financialsテーブルを受け取る、という動きを行ごとに繰り返しているのがわかりました！
②サンプルファイル
このフォルダの「最初のファイル」が入っています。{}の中は行のインデックス値です。
サンプルファイルは何に利用されるのかというと、カスタム関数呼び出し側でテーブルを展開するときの列名リストを取得に使われています！大事！
「ファイルの結合」ボタンをクリックしたときに、こんなウィンドウが出ましたよね。
実はここでサンプルファイルの選択をしていたのです。
ここで「最初のファイル」以外、例えば2013年のファイルを選択すると、
サンプルファイルの中身はこうなります。
※ちなみにこのサンプルファイルはバイナリです。
バイナリのクエリを作るには、Content列のBinaryをクリックします。
ファイルの中身が展開されるので、「インポートされたExcelブック」のステップを削除します。
②パラメーター1(サンプルファイル)
カスタム関数で利用するパラメーターです。
①のサンプルファイルがデフォルトで設定されています。
サンプルファイルを複数作ると、このように選ぶことができます。
※ここで規定のファイルを変更しても、カスタム関数呼び出し側で列名リストを取得する際に利用するファイルは変わりません。
④サンプルファイルの変換
「ファイルの変換」カスタム関数の出力結果の見本であると同時に、
ここで行った設定の変更を「ファイルの変換」カスタム関数にも反映してくれます。
ソース = Excel.Workbook(パラメーター1, null, true),
バイナリファイルパラメーターの指定、ファイルの形式の指定
financials_Table = ソース{[Item="financials",Kind="Table"]}[Data]
開くテーブルの指定
Excelファイルだとあまり恩恵はないかもしれないですが、
例えばCSVファイルだとこのあたりが変更できたりします。
おまけ：カスタム関数も便利
「ファイルの結合」はちょっと複雑ですが、カスタム関数とは、他のクエリからパラメーターを渡すことで呼び出し可能なクエリ、だと思っています。
行ごと、もしくはクエリごとに同じような処理をしなければならないときに便利です。
①カスタム関数の作成
クエリの詳細エディターでパラメーターを定義すると、勝手にアイコンが変更されてカスタム関数になります。
上記は、開始日と終了日を渡すことで、その期間内のデータを取得するSQLを使ったカスタム関数です。
ここでパラメーターを入力して「呼び出し」をクリックすると、実行結果が新規クエリとして追加されます。
②カスタム関数の呼び出し
さて、週の開始日と終了日のあるテーブルがここにあります。
「列の追加」タブから「カスタム変数の呼び出し」をクリックします。
テーブルの列をカスタム関数のパラメーターとして指定できます。
各行の指定列をパラメータとしたカスタム関数の実行結果が列追加されます。
今回のカスタム関数の取得結果は1行ですがテーブルなので、テーブル型の列が追加されています。
テーブル列を展開すると、期間ごとの値が取れています！
まとめ
むやみに長くなってしまいました。
フォルダーコネクタおよびファイルの結合は便利ですが、ヘルパークエリ内でエラーが発生することもたまにあるので、
中身で何をしているか知っておくのもいいかなと思っています。
先日 レポート更新時にこんなエラーが出ました。
Column 'XXXXXXX' in Table 'XXXXXXX' contains blank values and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.
Desktopで開いてみると、「サンプルファイルの変換」でエラーになっていて、データソースのフォルダ内に異なる文字コードのファイルが追加されたことが原因でした。
カスタム関数も堂々とリボンにある割には長らく謎のままの機能だったので、うまく使っていきたい所存です。
