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言語)入門」の記事

別ブックの最終シートの取り込み

3本目 別ブックの最終シート(一番右の最後のシート)をパワクで取り込んでください。バス・ブック名の指定は任意 シート数が変化しても、とにかく一番最後のシートを取り込むという事です。#パワク#PowerQuery 解答コード let ソース=Excel.Workbook(File.Contents("D:\パ…
列数不定のCSVの取り込み
4本目 列数不定のCSVをパワクで取り込んでください。1行目をヘッダーして使用してください。※バス・ファイル.名の指定は任意 ※文字コードはUTF8(BOMなし) ※テストデータはメモ帳等で作成してください。
CSVのA列が日付の行だけを取り込む
5本目 列数不定のCSVのA列が日付の行だけをパワクで取り込んでください。4本目の続きです。4本目にA列日付で抽出するステッブを追加してください。A列は日付型で取り込んでください。A列の列名は任意。#PowerQuery#M言語 解答コード let ソース=Csv.Document(File.Contents(&qu…
A列のヘッダー名を変更する
6本目 CSVをパワクで取り込みます。5本目の続きです。A列のヘッダーは本来は「日付」が正しいのですが、たまに「日付け」となっている場合もあるようです。※さらに他表記の場合もある。A列のヘッダを「日付」に統一して出力するようにしてください。
「売上」が数値の行のみ取り込む
7本目 CSVをパワクで取り込みます。列「売上」が非数値および空の行を削除して、「売上」が数値の行のみ取り込んでください。#PowerQuery#M言語 解答コード let ソース=Csv.Document(File.Contents("D:\パワク練習\CSV\test2.csv"),
2つのテーブルのマージ
8本目 ブック内に、「tbl個人」「tbl都道府県」があります。この2つのテーブルを結合して、添付サンブルのように出力してください。※「tbl個人」の都道府県には「県」の文字が入っていません。出力は「県」の入った県名で。
グルーブ内の最小・最大
9本目 ブック内に日付と商品コードを持つテーブル「tbl伝票」があります。商品コード毎の日付の最小値と最大値(最古日付と最新日付)を出力してください。※サンブルデータはALT #PowerQuery#M言語 解答コード let ソース=Excel.CurrentWorkbook(){[Name="tbl伝票…
グルーブ内の最小・最大
9本目 ブック内に日付と商品コードを持つテーブル「tbl伝票」があります。商品コード毎の日付の最小値と最大値(最古日付と最新日付)を出力してください。※サンブルデータはALT #PowerQuery#M言語 解答コード let ソース=Excel.CurrentWorkbook(){[Name="tbl伝票…
有効な最新単価の取得
10本目 「tbl伝票」(日付,コード) 「tblマスタ」(コード,日付,単価) マスタの日付は(これ以降有効となる)適用開始日です。「tbl伝票」に単価を付して出力してください。※例.2/5のA01はマスタの2/1が適用されます。
新旧マスタの差異比較
新旧マスタの差異比較


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

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)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




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


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



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