データウェアハウス構築でデータソースにワイルドカードや範囲指定の変換テーブルかませてファクトテーブルを作る(https://qiita.com/madilloar/items/56743b78e03212c239c7)
で、二つのテーブルのJOINにLIKE演算子やBETWEEN演算子を使って、オリジナルのデータソースのレコードにラベルを付与するようなSQLを作りました。
PowerQueryで同じようなことができるのでは?と思い試してみまして、自分的にはあらたな発見があったので覚書です。
どんな入力をして、どんな結果が欲しいか?
次のような2つのテーブルを
顧客名や商品名は"%"を使ったLIKE演算子で結合し、サイズはBETWEEN演算子で範囲指定して結合して、このようなテーブル(元の注文テーブルの右に分析用ラベルを付与したテーブル)を取得します。
結論:次のようなQueryになります
発見
- PowerQueryではテーブル同士の結合にLIKEやBETWEENのような結合が使えないので、テーブル同士をCROSS JOINで大きな表を作って、これをLIKEやBETWEENを再現する自作の関数を作ってフィルタできること。
- PowerQueryで自作の関数が作れること。
- PowerQueryで副問い合わせのQueryを作って、それとメインのテーブルを結合してフィルタできること。
- PowerQueryでGROUP BYやORDER BYを再現できること。
Q_注文に分析用ラベル付与するサンプル
下記は詳細エディタの内容です。
Q_注文に分析用ラベル付与するサンプル
let
MainTable = Excel.CurrentWorkbook(){[Name="T_注文"]}[Content],
SubTable = Excel.CurrentWorkbook(){[Name="T_分析用ラベル付与"]}[Content],
// 1:CROSS JOINをM言語で再現する。
// メインテーブルとサブテーブルの結合キー列でLIKEやBETWEENによるあいまいな結合が使えないので、いったんCROSS JOINで大きな表を作る。
CrossJoined = Table.AddColumn(MainTable, "Sub", each SubTable),
// 2:CROSS JOINで作った大きな表で、サブテーブルのカラムを使えるようにExpandする。
ExpandColumns = Table.ExpandTableColumn(CrossJoined, "Sub", {"優先順位", "顧客名", "商品名", "サイズFROM", "サイズTO", "分析用ラベル"},
{"Sub.優先順位", "Sub.顧客名", "Sub.商品名", "Sub.サイズFROM", "Sub.サイズTO", "Sub.分析用ラベル"}),
// 3:SQLのINNER JOIN句でLIKE演算子やBETWEEN演算子をM言語で再現する。
// LIKE演算子はFuzzyCompare関数、BETWEEN演算子はBetween関数。
// CROSS JOINで作った大きな表で、メインとサブの結合キー列をFuzzyCompare関数、Between関数で結合する。
FuzzyJoined = Table.SelectRows(ExpandColumns, each FuzzyCompare([顧客名], Text.Trim([Sub.顧客名]))
and FuzzyCompare([商品名], Text.Trim([Sub.商品名]))
and Between([サイズ], [Sub.サイズFROM], [Sub.サイズTO])),
// 4:SQLの副問い合わせをM言語で再現する。
// この例では副問い合わせはGROUP BYをM言語で再現している。
SubQuery = Table.Group(FuzzyJoined, {"注文ID"},{{"MIN優先順位", each List.Min([Sub.優先順位]), type number}}),
// 5:メインテーブルを副問い合わせでフィルタリングするSQLをM言語で再現する。
JoinedTable = Table.NestedJoin(ExpandColumns, {"注文ID", "Sub.優先順位"}, SubQuery, {"注文ID", "MIN優先順位"}, "SubQueryColumn", JoinKind.Inner),
// 6:SQLのSELECT句をM言語で再現する。
SelectedTable = Table.SelectColumns(JoinedTable, {"注文ID", "顧客名", "商品名", "サイズ", "本数", "Sub.分析用ラベル"}),
// 7:SQLのORDER BYをM言語で再現する。
SortedTable = Table.Sort(SelectedTable, {{"注文ID", Order.Ascending}})
in
SortedTable
Queryから呼び出される自作関数:FuzzyCompare
FuzzyCompare
(left as nullable text, right as nullable text, optional comparer as nullable function) as nullable logical =>
let
Result = if (right <> null) and (left <> null) then
// 末尾%で終わってたら、
if Text.EndsWith(right, "%") then
// 末尾%を取り除いた文字列でleftが始まっているか判定する
Text.StartsWith(left, Text.RemoveRange(right, Text.Length(right) - 1, 1), comparer)
// 先頭が%で始まっていたら、
else if Text.StartsWith(right, "%") then
// 先頭の%を取り除いた文字列でleftが終わっているか判定する
Text.EndsWith(left, Text.RemoveRange(right, 0, 1), comparer)
else
// %が両端になければ、単純に比較する
right = left
else
null
in
Result
Queryから呼び出される自作関数:Between
Between
(target as nullable number, left as nullable number, right as nullable number) as nullable logical =>
let
Result = if (target <> null) and (right <> null) and (left <> null) then
if left <= target and target <= right then
true
else
false
else
null
in
Result