新旧マスタの差異比較
「パワク11本目」
Power Query(M言語)の問題と解答・解説
ツイッターで問題を出して、それに解答と解説をしていく形でPower QueryとM言語の理解を深めていきます。
ページ内目次
パワク10本目

2つのテーブル「TBL新」「TBL旧」
統合して差異の区分を追加してください。
TBL旧のみ:"削除"
TBL新のみ:"新規"
両方で内容違い:"変更"(名称等はTBL新を出力)
新旧同じ:空欄
※サンブルデータはALT
#PowerQuery #M言語
id | 取引先名 | 住所 |
S001 | 取引先01 | 東京都 |
S002 | 取引先02 | 大阪府 |
S003 | 取引先03 | 神奈川県 |
S004 | 取引先04 | 宮城県 |
S006 | 取引先06 | 愛知県 |
id | 取引先名 | 住所 |
S001 | 取引先01 | 東京都 |
S003 | 取引先03 | 神奈川県 |
S004 | 取引先04 | 宮崎県 |
S005 | 取引先05 | 福岡県 |
S006 | 取引先06 | 愛知県 |
S007 | 取引先07 | 京都府 |

解答コード:FullOuter

let
TBL旧 = Excel.CurrentWorkbook(){[Name="TBL旧"]}[Content],
TBL新 = Excel.CurrentWorkbook(){[Name="TBL新"]}[Content],
TBL新旧 = let ソース = Table.NestedJoin(TBL旧, {"id"}, TBL新, {"id"}, "TBL新", JoinKind.FullOuter),
列名変更 = Table.RenameColumns(ソース,{{"id", "TBL旧.id"}, {"取引先名", "TBL旧.取引先名"}, {"住所", "TBL旧.住所"}}),
結合展開 = Table.ExpandTableColumn(列名変更, "TBL新", {"id", "取引先名", "住所"}, {"TBL新.id", "TBL新.取引先名", "TBL新.住所"})
in 結合展開,
列追加 = let 追加id = Table.AddColumn(TBL新旧, "id", each if [TBL新.id]=null then [TBL旧.id] else [TBL新.id]),
追加取引先名 = Table.AddColumn(追加id, "取引先名", each if [TBL新.取引先名]=null then [TBL旧.取引先名] else [TBL新.取引先名]),
追加住所 = Table.AddColumn(追加取引先名, "住所", each if [TBL新.住所]=null then [TBL旧.住所] else [TBL新.住所]),
追加区分 = Table.AddColumn(追加住所, "区分", each if [TBL旧.取引先名]=[TBL新.取引先名] and [TBL旧.住所]=[TBL新.住所] then ""
else if [TBL旧.取引先名]=null then "新規"
else if [TBL新.取引先名]=null then "削除"
else "変更")
in 追加区分,
列削除 = Table.RemoveColumns(列追加,{"TBL旧.id", "TBL旧.取引先名", "TBL旧.住所", "TBL新.id", "TBL新.取引先名", "TBL新.住所"}),
出力順 = Table.Sort(列削除,{{"id", Order.Ascending}})
in
出力順
解答コード:Table.Combine

let
TBL旧 = Excel.CurrentWorkbook(){[Name="TBL旧"]}[Content],
TBL新 = Excel.CurrentWorkbook(){[Name="TBL新"]}[Content],
TBL新旧 = let ソース = Table.Combine({TBL新, TBL旧}, {"id"}),
重複削除 = Table.Distinct(ソース, {"id"}),
マージ旧 = Table.NestedJoin(重複削除, {"id"}, TBL旧, {"id"}, "TBL旧", JoinKind.LeftOuter),
マージ新 = Table.NestedJoin(マージ旧, {"id"}, TBL新, {"id"}, "TBL新", JoinKind.LeftOuter),
展開旧 = Table.ExpandTableColumn(マージ新, "TBL旧", {"取引先名", "住所"}, {"TBL旧.取引先名", "TBL旧.住所"}),
展開新 = Table.ExpandTableColumn(展開旧, "TBL新", {"取引先名", "住所"}, {"TBL新.取引先名", "TBL新.住所"})
in 展開新,
列追加 = let 追加取引先 = Table.AddColumn(TBL新旧, "取引先名", each if [TBL新.取引先名]=null then [TBL旧.取引先名] else [TBL新.取引先名]),
追加住所 = Table.AddColumn(追加取引先, "住所", each if [TBL新.住所]=null then [TBL旧.住所] else [TBL新.住所]),
追加区分 = Table.AddColumn(追加住所, "区分", each if [TBL旧.取引先名]=[TBL新.取引先名] and [TBL旧.住所]=[TBL新.住所] then ""
else if [TBL旧.取引先名]=null then "新規"
else if [TBL新.取引先名]=null then "削除"
else "変更")
in 追加区分,
列削除 = Table.RemoveColumns(列追加,{"TBL旧.取引先名", "TBL旧.住所", "TBL新.取引先名", "TBL新.住所"}),
出力順 = Table.Sort(列削除,{{"id", Order.Ascending}})
in
出力順
GUI操作での作成:FullOuter
「クエリの結合」→「マージ」
完全外部(両方の行全て)を選択します。





・id
・取引先名
・住所
・区分







完成したスクリブトが以下になります。
最初の解答コードは、これを基に手動でまとめたものになります。

let
ソース = Table.NestedJoin(TBL旧, {"id"}, TBL新, {"id"}, "TBL新", JoinKind.FullOuter),
#"展開された TBL新" = Table.ExpandTableColumn(ソース, "TBL新", {"id", "取引先名", "住所"}, {"TBL新.id", "TBL新.取引先名", "TBL新.住所"}),
#"名前が変更された列 " = Table.RenameColumns(#"展開された TBL新",{{"id", "TBL旧.id"}, {"取引先名", "TBL旧.取引先名"}, {"住所", "TBL旧.住所"}}),
追加されたカスタム = Table.AddColumn(#"名前が変更された列 ", "id", each if [TBL新.id]=null then [TBL旧.id] else [TBL新.id]),
追加されたカスタム1 = Table.AddColumn(追加されたカスタム, "取引先名", each if [TBL新.取引先名]=null then [TBL旧.取引先名] else [TBL新.取引先名]),
追加されたカスタム2 = Table.AddColumn(追加されたカスタム1, "住所", each if [TBL新.住所]=null then [TBL旧.住所] else [TBL新.住所]),
追加されたカスタム3 = Table.AddColumn(追加されたカスタム2, "区分", each if [TBL旧.取引先名]=[TBL新.取引先名] and [TBL旧.住所]=[TBL新.住所] then ""
else if [TBL旧.取引先名]=null then "新規"
else if [TBL新.取引先名]=null then "削除"
else "変更"),
削除された列 = Table.RemoveColumns(追加されたカスタム3,{"TBL旧.id", "TBL旧.取引先名", "TBL旧.住所", "TBL新.id", "TBL新.取引先名", "TBL新.住所"})
in
削除された列
GUI操作での作成:Table.Combine
「クエリの結合」→「追加」













・取引先名
・住所
・区分






最初の解答コードは、これを基に手動でまとめたものになります。

let
ソース = Table.Combine({TBL旧, TBL新}),
削除された列 = Table.RemoveColumns(ソース,{"取引先名", "住所"}),
削除された重複 = Table.Distinct(削除された列),
マージされたクエリ数 = Table.NestedJoin(削除された重複, {"id"}, TBL旧, {"id"}, "TBL旧", JoinKind.LeftOuter),
マージされたクエリ数1 = Table.NestedJoin(マージされたクエリ数, {"id"}, TBL新, {"id"}, "TBL新", JoinKind.LeftOuter),
#"展開された TBL旧" = Table.ExpandTableColumn(マージされたクエリ数1, "TBL旧", {"取引先名", "住所"}, {"TBL旧.取引先名", "TBL旧.住所"}),
#"展開された TBL新" = Table.ExpandTableColumn(#"展開された TBL旧", "TBL新", {"取引先名", "住所"}, {"TBL新.取引先名", "TBL新.住所"}),
追加されたカスタム = Table.AddColumn(#"展開された TBL新", "取引先名", each if [TBL新.取引先名]=null then [TBL旧.取引先名] else [TBL新.取引先名]),
追加されたカスタム1 = Table.AddColumn(追加されたカスタム, "住所", each if [TBL新.住所]=null then [TBL旧.住所] else [TBL新.住所]),
追加されたカスタム2 = Table.AddColumn(追加されたカスタム1, "区分", each if [TBL旧.取引先名]=[TBL新.取引先名] and [TBL旧.住所]=[TBL新.住所] then ""
else if [TBL旧.取引先名]=null then "新規"
else if [TBL新.取引先名]=null then "削除"
else "変更"),
削除された列1 = Table.RemoveColumns(追加されたカスタム2,{"TBL旧.取引先名", "TBL旧.住所", "TBL新.取引先名", "TBL新.住所"})
in
削除された列1
Table.Combine
結果として得たテーブルの行型の構造は、columns によって定義されます。
または、columns が指定されていない場合は、入力型の論理和になります。
tables as list
ここに結合するテーブルをリスト形式で指定します。
Table.Combine({TBL旧, TBL新})
Table.Distinct
省略可能なパラメーター equationCriteria は、テーブルのどの列に対して重複をテストするかを指定します。
equationCriteria が指定されていない場合は、すべての列がテストされます。
今回は1列デーフなので、特に気にするところはないと思います。
同じテーマ「PowerQuery(M言語)入門」の記事
別ブックの最終シートの取り込み
列数不定のCSVの取り込み
CSVのA列が日付の行だけを取り込む
A列のヘッダー名を変更する
「売上」が数値の行のみ取り込む
2つのテーブルのマージ
グルーブ内の最小・最大
グルーブ内の最小・最大
有効な最新単価の取得
新旧マスタの差異比較
新旧マスタの差異比較
新着記事NEW ・・・新着記事一覧を見る
列全体を指定する時のRangeとColumnsの違い|ツイッター出題回答 (2023-09-24)
シートのActiveXチェックボックスの指定方法|ツイッター出題回答 (2023-09-24)
ByRef引数の型が一致しません。|ツイッター出題回答 (2023-09-22)
シートコピー後のアクティブシートは何か|ツイッター出題回答 (2023-09-19)
Excel関数の引数を省略した場合について|ツイッター出題回答 (2023-09-14)
セル個数を返すRange.CountLargeプロパティとは|VBA技術解説(2023-09-08)
記号を繰り返してグラフ作成(10単位で折り返す)|ツイッター出題回答 (2023-08-28)
シートを削除:不定数のシート名に対応|VBAサンプル集(2023-08-24)
ランクによりボイントを付ける(同順位はポイントを分割)|ツイッター出題回答 (2023-08-22)
OneDrive使用時のThisWorkbook.Pathの扱い方|VBA技術解説(2023-07-26)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.繰り返し処理(For Next)|VBA入門
4.変数宣言のDimとデータ型|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.マクロとは?VBAとは?VBAでできること|VBA入門
7.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
8.並べ替え(Sort)|VBA入門
9.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
10.条件分岐(IF)|VBA入門
- ホーム
- その他
- PowerQuery(M言語)入門
- 新旧マスタの差異比較
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。