目標
今回のテーマは同じフォーマットで異なる2点間のデータがある場合、それらをユニークキーでつないで項目ごとの比較を自動化することです。
用途として以下のようなケースが考えられます。
- ERPや営業支援システム導入プロジェクトの移行データの検証
- 2点間のマスタデータを比較して項目ごとの変更点を調べる
例えば以下のようなデータがあったとします。
前回のデータ
今回のデータ（変更点を太字にしています）
この2点間のデータをユニークキー（ここではID)を元に比較し、項目ごとの差異を自動検出して表にします。
技術的なポイント
ユニークキーと項目の組み合わせを作り、前回と今回の値をペアにして横に並べて比較します。
そのためには、ピボット解除、列のピボットを駆使して表を組み替える必要があります。
その時、パワークエリでは同じ列名が登録されると末尾に「.1]が付くのでそれを利用して並び替えることがポイントです。
- キー項目でマージ
- マージしたテーブルを展開するときにユニークキーを除き、またプレフィックスを使わない。
- ユニークキー以外をピボット解除
- ユニークキーと属性（項目名）でソート
- インデックス列を追加して列のピボットの準備
- 列のピボットで対比表を作る
- 条件列で差異のあるレコードのみに絞る
実践
比較する二つのテーブルのマージ
まず、前回と今回のデータをそのまま読み込むクエリを作り、結合（クエリのマージ）の準備をします。
今回は接続専用としていますが、データをExcelワークシート上で確認したい場合は、テーブルに読み込んでおくとよいでしょう。
次に、クエリのマージを行います。
データの取得 → クエリの結合 → マージ
前回と今回のクエリを選択し、ID列を照合列としてマージします。
結合の種類については今回はユニークキーのあるもののみを対象にしていますので、内部（一致する行のみ）を選択します。
二つのテーブルがIDでマージされた結果が表示されますので、今回列の右の矢印をクリックして展開します。
展開の際はIDのチェックを外し、また元の列名をプレフィックスとして使用します。のチェックを外してください。
こうすることで展開された項目名の末尾にすべて「.1」が追加されました。あとで並び替えをするときにこれを利用します。
ピボット解除＆列のピボットで表の組み替え
次にID列を選択し、右クリックを押してその他の列のピボット解除を実行します。
こうするとID列ごとに、名前と名前.1、住所と住所.1・・・のペアが並びます。
続いてID右上の▼をクリックして昇順で並び替え、さらに属性の▼をクリックして昇順で並び替えで二つの項目で並び替えを行います。
二つの項目を使って並び替えを行う場合、それぞれの項目に優先順位として1 , 2 が表示されます。
これで前回と今回の同じ項目のペアが上下に並びました。これからそのペアを横に並べます。
まず列のピボットの相棒、インデックス列を追加します。開始は０でも1でも構いません。今回は0で進めます。
列の追加 → インデックス列
右端に0から始まる連番が追加されました。
続いてインデックス列を選択した状態で以下の手順で０，１のリズムを作ります。
項目が縦にペアで並んでいるので、それぞれに番号を割り当てます。
列の追加 → 数値から → 標準 → 剰余
2を入力します。
すると、先の名前と名前.1、住所と住所.1・・・のペアの対応する形でそれぞれ、0と1が割り当てられます。
ここまで来たら、属性の「.1」はもう使用しないので値の置換で消します。
属性列を選び、右クリック → 値の置換で検索する値に「.1｣、置換後は何も入力しないでOKを押します。
これで名前が統一されました。
つづいてインデックス列も用済みなので列の削除で削除します。インデックス列を選択して列の削除を行います。
これで列のピボットの準備ができました。
列のピボットでは最初に、ヘッダー（列名）になる列を選択します。
今回は先ほどインデックスを元に作成した剰余を選択します。この０と１が列名として横に並ぶことになります。
、変換 → 列のピボットを開き、値に値、詳細オプションを開いて、値の集計関数を集計しないにしてOKを押します。
これで比較項目が左右に並びました！
仮にあてた列名（0と1）をそれぞれ前回と今回に変更します。
変更の有り無しチェック
最後に仕上げの比較です。
列の追加 → 条件列で以下のように入力して前回と今回の変更をチェックします。
これでID×項目単位での前回と今回の比較ができました。
最後に変更チェックのフィルターを掛け、変更のあるもの（ちがう）だけを表示します。
変更チェック列はもう使用しないので列の削除で削除します。
これで閉じて読み込むを実行して出来上がりです。
関数化してエクスポート＆共有
今回のクエリを再利用するため、関数化とエクスポートによるチーム内での共有を行います。
クエリ関数化
まずは以下の手順で関数化します。
先ほどのクエリを開き、詳細エディターを開いてletの前に以下の文を追加してください。
関数の引数として宣言されているtable型の前回と今回はそれぞれ、先ほど作ったクエリの最初のマージのテーブル名と一致していることに注意ください。
(前回 as table, 今回 as table) as table =>
これで、前回と今回のテーブルを選択するとその差分をピックアップする関数になりました。
試しに前回、今回にテーブルを選択し、呼び出しをクリックしてみてください。
新しいクエリが呼び出された関数として作成され、結果が表示されました。
クエリのエクスポート
続いて作成した関数をエクスポートしてチーム内で共有します。
PowerQuery エディターを閉じて、クエリと接続ペインで先ほど作った関数を右クリックし、接続ファイルのエクスポートを実行し、共有フォルダに保存します。
クエリのインポート
共有フォルダ内のクエリのエクスポートファイルを別な人がインポートするときは既存の接続を選びます。
参照を押して保存されている場所を開き、ファイルを選択します。
インポート先ですが、今回は関数なので接続の作成のみを選択します。
これでクエリがインポートされました。
エクスポートファイルはチーム内で共有するなどしてデータとプロセスの統一を図るとよいでしょう。
ソースコード
今回のソースコード
以下が今回のクエリのコードとなります。詳細エディターにコピペすればそのまま使えます。
(前回 as table, 今回 as table) =>
let
// 前回と今回テーブルのマージ
ソース = Table.NestedJoin(前回, {"ID"}, 今回, {"ID"}, "今回", JoinKind.Inner),
#"展開された 今回" = Table.ExpandTableColumn(ソース, "今回", {"名前", "住所", "売上", "利益"}, {"名前.1", "住所.1", "売上.1", "利益.1"}),// 表の組み換え
ピボット解除された他の列 = Table.UnpivotOtherColumns(#"展開された 今回", {"ID"}, "属性", "値"),
並べ替えられた行 = Table.Sort(ピボット解除された他の列,{{"ID", Order.Ascending}, {"属性", Order.Ascending}}),
追加されたインデックス = Table.AddIndexColumn(並べ替えられた行, "インデックス", 0, 1),
挿入された剰余 = Table.AddColumn(追加されたインデックス, "剰余", each Number.Mod([インデックス], 2), type number),
置き換えられた値 = Table.ReplaceValue(挿入された剰余,".1","",Replacer.ReplaceText,{"属性"}),
削除された列 = Table.RemoveColumns(置き換えられた値,{"インデックス"}),
ピボットされた列 = Table.Pivot(Table.TransformColumnTypes(削除された列, {{"剰余", type text}}, "ja-JP"), List.Distinct(Table.TransformColumnTypes(削除された列, {{"剰余", type text}}, "ja-JP")[剰余]), "剰余", "値"),
#"名前が変更された列 " = Table.RenameColumns(ピボットされた列,{{"0", "前回"}, {"1", "今回"}}),// 変更有り無しチェック
追加された条件列 = Table.AddColumn(#"名前が変更された列 ", "変更チェック", each if [前回] = [今回] then "同じ" else "ちがう"),
フィルターされた行 = Table.SelectRows(追加された条件列, each ([変更チェック] = "ちがう")),
削除された列1 = Table.RemoveColumns(フィルターされた行,{"変更チェック"})
in
削除された列1
汎用化したソースコード
テーブルの列数の変化に耐えられるように汎用化したソースコードです。
パラメーターとしてユニークキーの指定が必要になります。
(前回 as table, 今回 as table, ユニークキー as text) =>
let// パラメータのセット
// 前回 = 前回,
// 今回 = 今回,
// ユニークキー = "ID",// 前回と今回テーブルのマージ
ソース = Table.NestedJoin(前回, {ユニークキー}, 今回, {ユニークキー}, "今回", JoinKind.Inner),// ユニークキーを除いた列名リストを作成
列名一覧 = List.RemoveMatchingItems(Table.ColumnNames(前回), {ユニークキー}),// マージ後列名リストを作成 (List.Generate版)
// マージ後列名一覧 = List.Generate(
// () => [x=0, y = 列名一覧{0} & ".1"],
// each [x] < List.Count(列名一覧) ,
// each [x = [x] + 1, y= 列名一覧{x} & ".1"],
// each [y]
// ),
// マージ後列名リストを作成 (List.Transform版)
マージ後列名一覧 = List.Transform(列名一覧, each _ &".1"),// テーブルをマージ
#"展開された 今回" = Table.ExpandTableColumn(ソース, "今回", 列名一覧, マージ後列名一覧),// 表の組み換え(ユニークキーでピボット解除)
ピボット解除された他の列 = Table.UnpivotOtherColumns(#"展開された 今回", {ユニークキー}, "属性", "値"),// 表の組み換え（列のピボット準備）
並べ替えられた行 = Table.Sort(ピボット解除された他の列,{{ユニークキー, Order.Ascending}, {"属性", Order.Ascending}}),
追加されたインデックス = Table.AddIndexColumn(並べ替えられた行, "インデックス", 0, 1),
挿入された剰余 = Table.AddColumn(追加されたインデックス, "剰余", each Number.Mod([インデックス], 2), type number),
置き換えられた値 = Table.ReplaceValue(挿入された剰余,".1","",Replacer.ReplaceText,{"属性"}),
削除された列 = Table.RemoveColumns(置き換えられた値,{"インデックス"}),// 表の組み換え（列のピボット）
ピボットされた列 = Table.Pivot(Table.TransformColumnTypes(削除された列, {{"剰余", type text}}, "ja-JP"), List.Distinct(Table.TransformColumnTypes(削除された列, {{"剰余", type text}}, "ja-JP")[剰余]), "剰余", "値"),
#"名前が変更された列 " = Table.RenameColumns(ピボットされた列,{{"0", "前回"}, {"1", "今回"}}),// 変更有り無しチェック
追加された条件列 = Table.AddColumn(#"名前が変更された列 ", "変更チェック", each if [前回] = [今回] then "同じ" else "ちがう"),
フィルターされた行 = Table.SelectRows(追加された条件列, each ([変更チェック] = "ちがう")),
削除された列1 = Table.RemoveColumns(フィルターされた行,{"変更チェック"}),// 項目を列に再び移動してフォーマット調整
結合された列 = Table.CombineColumns(Table.TransformColumnTypes(削除された列1, {{"前回", type text}, {"今回", type text}}, "ja-JP"),{"前回", "今回"},Combiner.CombineTextByDelimiter(" -> ", QuoteStyle.None),"値"),
ピボットされた列1 = Table.Pivot(結合された列, List.Distinct(結合された列[属性]), "属性", "値"),// 仕上げの列の並び替え（差異のあった列を元の順番に戻す）
最終列名一覧 = List.Intersect({列名一覧, Table.ColumnNames(ピボットされた列1)}),
比較結果 = Table.ReorderColumns(ピボットされた列1, {ユニークキー} & 最終列名一覧)
in
比較結果
