新旧マスタの差異比較｜Power Query（M言語）入門

最終更新日：2023-02-28

新旧マスタの差異比較

「パワク11本目」
Power Query（M言語）の問題と解答・解説

本シリーズは、ツイッターと連動企画になります。
ツイッターで問題を出して、それに解答と解説をしていく形でPower QueryとM言語の理解を深めていきます。

ページ内目次

パワク11本目
解答コード：FullOuter
解答コード：Table.Combine
GUI操作での作成：FullOuter
GUI操作での作成：Table.Combine
Table.Combine
Table.Distinct

パワク10本目

#パワク11本目
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

まずは「TBL旧」「TBL新」それぞれのクエリを作製してから以下の操作に入ります。
「クエリの結合」→「マージ」
完全外部（両方の行全て）を選択します。

マージで追加したテーブルを展開

「TBL旧」の方の列名を出力する列名と重ならないように変更しておきます。

カスタム列の追加
・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

まずは「TBL旧」「TBL新」それぞれのクエリを作製してから以下の操作に入ります。
「クエリの結合」→「追加」

「id」以外の列を削除して「重複の削除」

「クエリのマージ」で「TBL旧」「TBL新」をマージします。

マージしたテーブルを展開します。「id」は不要

カスタム列の追加
・取引先名
・住所
・区分

出力に必要なのはidと追加した列だけなので、マージした元の列は削除。

出力順に並べ替え。

今回のデータでは偶然目的の順になっているだけなので並べ替えは必要です。

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

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

※Learn Table.Combine

構文

Table.Combine(tables as list, optional columns as any) as table

テーブルのリスト (tables) をマージした結果として得たテーブルが返されます。
結果として得たテーブルの行型の構造は、columns によって定義されます。
または、columns が指定されていない場合は、入力型の論理和になります。

複数のテーブルを縦に結合します。
tables as list
ここに結合するテーブルをリスト形式で指定します。
Table.Combine({TBL旧, TBL新})

Table.Distinct

※Learn Table.Distinct

構文

Table.Distinct(table as table, optional equationCriteria as any) as table

重複する行をテーブルから削除します。
省略可能なパラメーター equationCriteria は、テーブルのどの列に対して重複をテストするかを指定します。
equationCriteria が指定されていない場合は、すべての列がテストされます。

※Power Query はバックエンドデータソースに特定の操作をオフロードすることがあり (折りたたみと呼ばれます)、また、厳密に必要ではない操作をスキップしてクエリを最適化する場合があるため、一般に、特定の重複が保持される保証はありません。

重複の削除については注意したほうが良いと思いますが、
今回は1列デーフなので、特に気にするところはないと思います。

同じテーマ「PowerQuery（M言語）入門」の記事

セル値でパス・ブック・シート名を指定
 別ブックの最終シートの取り込み
 列数不定のCSVの取り込み
 CSVのA列が日付の行だけを取り込む
 A列のヘッダー名を変更する
 「売上」が数値の行のみ取り込む
 2つのテーブルのマージ
 グルーブ内の最小・最大
 グルーブ内の最小・最大
 有効な最新単価の取得
新旧マスタの差異比較

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.RangeとCellsの使い方｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.繰り返し処理（For Next)｜VBA入門
5.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
6.マクロって何？VBAって何？｜VBA入門
7.並べ替え（Sort）｜VBA入門
8.Excelショートカットキー一覧｜Excelリファレンス
9.エクセルVBAでのシート指定方法｜VBA技術解説
10.Range以外の指定方法（Cells,Rows,Columns）｜VBA入門

ホーム
その他
PowerQuery（M言語）入門
新旧マスタの差異比較

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。

Power Query（M言語）入門
新旧マスタの差異比較