追加されたPower Queryクエリの自動結合

Power Queryを集計する方法は過去いろいろ話してきましたが、中でも便利な関数の1つにTable.Combineがあります。このブログで何度も紹介していますが、複数のレコードでテーブル型の列に対して、

Table.Combine(前ステップ[列名])
※前ステップ[列名]はリスト

と記述するとヘッダーの昇格が終わっているクエリ同士を結合することができます。Power Queryでデータを結合する際のベストプラクティスがこの関数を使ったものですが、下図のように、t_01とt_02というクエリを結合（Append）したクエリAllに対して、t_03というクエリが追加されただけで、Allにもt_03のデータが自動追加されるやり方を紹介します。

なお、このやり方はTable.Combine( { t_01, t_02, t_03 } )のように行うことも可能ですが、このようにマニュアルでクエリ名を追加しないやり方となります。大量に同じような名前のクエリがある場合に重宝できると言えます。

なお、ExcelとPower BIでは挙動が異なるため、Power BIを使う方はかなり注意が必要です。

セットアップ
落とし穴

セットアップ

Excelで以下3つのテーブルを作り、クエリとして読み込みます（テーブル化・クエリ化のやり方はこちら）

Excel Power Queryエディタで開くと、以下のように見えます。

t_01を右クリック > 複製 >名前をt_02に変更し、Sourceクエリも以下のように変更。
= Excel.CurrentWorkbook(){[Name="t_02"]}[Content]

Allというクエリを作りますが、#sectionsというキーワード（組み込み変数）を使用します。M言語には、予約されている特定の単語があり、特定の文脈でのみ使用することができますが、#sectionsと同じキーワードに#sharedがあり、こちらで解説しています。なお、クエリAllは#sharedを使用しても、#sectionsを使用しても実現できますが、今回は#sectionsを使用します。

クエリエディタを右クリック > 新しいクエリ > その他のソース > 空のクエリ > #sections[Section1]と入力

見ての通り、作ったt_01、t_02というクエリがレコードとして抽出される一方、新規に作った「空のクエリ」が「クエリ1」となって生成されました。ここで以下のようにテーブルに変換しておきます。

Name列選択し、「指定の値で始まる」をクリック

t_を入力し、OK

これでクエリ名がt_で始まるものを全て抽出することができるようになりました。

この後、Name列をValue列に挿入し、Table.Combineを使用してNameまで過不足なく抽出して全てのデータを結合しますが、最終的なAllのコードは以下のようになります。

//All
let
    Source = #sections[Section1],
    ToTable = Record.ToTable(Source),
    Filtered = Table.SelectRows(ToTable, each Text.StartsWith([Name], "t_")),
    GetData = Table.AddColumn(Filtered, "Data", each Table.AddColumn([Value], "QueryName", (x)=>[Name])),
    Combined = Table.Combine(GetData[Data])
in
    Combined

ここで試しにt_02を複製し、名前とソース参照先をそれぞれt_03に変更。
= Excel.CurrentWorkbook(){[Name="t_03"]}[Content]

Allをクリックし、更新ボタンを押すと、t_03が追加されていることを確認できます。

Excel Power Query、Excel Power Pivotをメインで作業される方はこの後、シートへ出力するなり、Power Pivotへ読み込むなりお好きなようにしてください。

落とし穴

このままであれば夢のようなやり方ですが、ここで1つ落とし穴があります。それは・・・

Power BI（Desktop、サービス両方）では使用できない

ことです。

Excelを使う人には最高の構築法でも、Power BIをメインとする人にとっては役に立たないものとなります。

まずはt_01～t_03をそれぞれコピーします（注: クエリ自体をコピーではなく、テーブルとしてコピー）。

下記①～⑦のように、Power BI Desktop側でもExcelと同じクエリを再現します。

ここまでは非常に順調で、Allだけを読み込み対象にします。

本来であれば、このまま「閉じて適用」でデータモデルにAllが読み込まれるはずですが、Power BI Desktopでは必ず以下のようなエラーが発生します。

このエラー、Excelでは起こらないのですが、Power BI Desktopでは#sharedや#sectionsといったキーワード関数を使用するとデータを読み込めない状態となります。

製品エンジニアチームに問い合わせたところ、以下のような返事がきました。

この方法は、動的なクエリやデータソースに関連するシナリオであり、Mashupエンジンの単純な静的分析にとどまらないため、全体のドキュメントを評価して使用されるデータソースを理解する必要があります。この問題はデータセットの制限として存在しており、設計によるものですが、変更には新しいアイデアが必要です。現在はその機能化が優先事項ではなく、参照されないクエリを削除しない場合に他の問題を引き起こす可能性があることが問題です。

というわけで、Power BI Desktopでは残念ながら使用することが出来ないテクニックとなります。製品チームとしては、Power BI Desktop上からは以下①～③のように、UIベース等で追加することをお勧めしています。