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

Power Query(パワークエリ)とM言語を練習問題を通して学んでいきます
公開日:2023-02-28 最終更新日:2023-02-28

新旧マスタの差異比較


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


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

パワク10本目

Excel エクセル Power Query M言語

#パワク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 京都府

Excel エクセル Power Query M言語


解答コード:FullOuter

Excel エクセル Power Query M言語
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

Excel エクセル Power Query M言語
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新」それぞれのクエリを作製してから以下の操作に入ります。
「クエリの結合」→「マージ」
完全外部(両方の行全て)を選択します。
Excel エクセル Power Query M言語

Excel エクセル Power Query M言語

マージで追加したテーブルを展開
Excel エクセル Power Query M言語

Excel エクセル Power Query M言語

「TBL旧」の方の列名を出力する列名と重ならないように変更しておきます。
Excel エクセル Power Query M言語

カスタム列の追加
・id
・取引先名
・住所
・区分
Excel エクセル Power Query M言語

Excel エクセル Power Query M言語

Excel エクセル Power Query M言語

Excel エクセル Power Query M言語

Excel エクセル Power Query M言語

出力に必要なのは追加した列だけなので、マージした元の列は削除。
Excel エクセル Power Query M言語

出力順に並べ替え
Excel エクセル Power Query M言語


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

Excel エクセル Power Query M言語
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新」それぞれのクエリを作製してから以下の操作に入ります。
「クエリの結合」→「追加」
Excel エクセル Power Query M言語

Excel エクセル Power Query M言語

Excel エクセル Power Query M言語

「id」以外の列を削除して「重複の削除」
Excel エクセル Power Query M言語

Excel エクセル Power Query M言語

「クエリのマージ」で「TBL旧」「TBL新」をマージします。
Excel エクセル Power Query M言語

Excel エクセル Power Query M言語

Excel エクセル Power Query M言語

Excel エクセル Power Query M言語

Excel エクセル Power Query M言語

マージしたテーブルを展開します。「id」は不要
Excel エクセル Power Query M言語

Excel エクセル Power Query M言語

Excel エクセル Power Query M言語

カスタム列の追加
・取引先名
・住所
・区分
Excel エクセル Power Query M言語

Excel エクセル Power Query M言語

Excel エクセル Power Query M言語

Excel エクセル Power Query M言語

出力に必要なのはidと追加した列だけなので、マージした元の列は削除。
Excel エクセル Power Query M言語

出力順に並べ替え
Excel エクセル Power Query M言語

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

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

Excel エクセル Power Query M言語
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

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

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

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


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つのテーブルのマージ
グルーブ内の最小・最大
グルーブ内の最小・最大
有効な最新単価の取得
新旧マスタの差異比較
新旧マスタの差異比較


新着記事NEW ・・・新着記事一覧を見る

エクセルが起動しない、Excelが立ち上がらない|エクセル雑感(2024-04-11)
ブール型(Boolean)のis変数・フラグについて|VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する|VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.RangeとCellsの使い方|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.メッセージボックス(MsgBox関数)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.ひらがな⇔カタカナの変換|エクセル基本操作




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


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


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