有効な最新単価の取得｜Power Query（M言語）入門

公開日：2023-02-21　最終更新日：2023-02-21

有効な最新単価の取得

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

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

ページ内目次

パワク10本目
解答コード
GUI操作での作成
letの活用と式の簡略化
Table.NestedJoin
Table.ExpandTableColumn
Table.SelectRows
Table.Group
Table.Sort

パワク10本目

#パワク10本目
「tbl伝票」（日付,コード）
「tblマスタ」（コード,日付,単価）
マスタの日付は（これ以降有効となる）適用開始日です。
「tbl伝票」に単価を付して出力してください。
※例.2/5のA01はマスタの2/1が適用されます。
※マスタは必ずあります。
※サンブルデータはALT
#PowerQuery #M言語

すみません、結果の画像が間違ったものを貼り付けていました。
（単にマスタをクエリでテーブルしただけの物になっていました。）
再添付します。

日付	コード
2023/02/01	A04
2023/02/02	A01
2023/02/03	A04
2023/02/04	A03
2023/02/05	A01
2023/02/06	A05
2023/02/07	A05
2023/02/08	A02
2023/02/09	A03
2023/02/10	A01
2023/02/11	A02
2023/02/12	A03
2023/02/13	A04
2023/02/14	A05
2023/02/15	A02

コード	日付	単価
A01	2023/01/01	100
A01	2023/02/01	130
A01	2023/02/07	150
A02	2023/01/01	210
A02	2023/02/11	200
A02	2023/02/21	230
A03	2023/01/01	160
A03	2023/02/17	130
A03	2023/02/25	180
A04	2023/01/01	320
A04	2023/02/21	280
A05	2023/01/01	500
A05	2023/02/03	450
A05	2023/02/05	420

解答コード

let
    // tbl伝票の取り込みと日付型への変更
    tbl伝票 = let ソース = Excel.CurrentWorkbook(){[Name="tbl伝票"]}[Content],
                  型変換 = Table.TransformColumnTypes(ソース,{{"日付", type date}})
            　in  型変換,
    // tblマスタの取り込みと日付型への変更
    tblマスタ = let ソース = Excel.CurrentWorkbook(){[Name="tblマスタ"]}[Content],
                    型変換 = Table.TransformColumnTypes(tblマスタ,{{"日付", type date}})
                in 型変換,
    // tbl伝票とtblマスタの結合、マスタの日付が伝票日付より大きい場合行は対象外
    tbl伝票マスタ = let ソース = Table.NestedJoin(tbl伝票, {"コード"}, tblマスタ, {"コード"}, "tblマスタ", JoinKind.LeftOuter),
                        展開 = Table.ExpandTableColumn(ソース, "tblマスタ", {"日付", "単価"}, {"tblマスタ.日付", "tblマスタ.単価"}),
                        フィルタ = Table.SelectRows(展開, each ([tblマスタ.日付]<=[日付]))
                   in  フィルタ,
    // 日付とコードでグループ7化し、有効なマスタ日付の最大日に絞り込む
    tbl伝票単価 = let グループ化 = Table.Group(tbl伝票マスタ, {"日付", "コード"}, 
                                                            {{"直近日付", each List.Max([tblマスタ.日付]), type nullable date}, 
                                                             {"単価", each _, type table [単価=nullable number]}}),
                      単価列展開 = Table.ExpandTableColumn(グループ化, "単価", {"tblマスタ.日付", "tblマスタ.単価"}, {"マスタ日付", "単価"}),
                      直近単価 = Table.SelectRows(単価列展開, each ([直近日付] = [マスタ日付])),
                      作業列削除 = Table.RemoveColumns(直近単価,{"直近日付", "マスタ日付"})
                  in 作業列削除,
    // 順序を整えて出力
    並べ替え = Table.Sort(tbl伝票単価,{{"日付", Order.Ascending}})
in
    並べ替え

GUI操作で作成されたクエリをもとに、手動で書き換えたものになります。
上記クエリで使われている主要な関数としては、
Table.NestedJoin
Table.ExpandTableColumn
Table.SelectRows
Table.Group
Table.Sort
これらになります。
詳しくは後述します。

GUI操作での作成

最初に「tbl伝票」と「tblマスタ」を取り込むクエリを作成しておきます。
「日付」は時刻不要なので日付型に変換します。

「tbl伝票」と「tblマスタ」結合

伝票の日付より未来のマスタは、その伝票には不要なので、これを除外します。

追加した「判定1」が「1」の場合は伝票より未来のマスタなので対象外。

「判定1」はもう不要なので列削除

「コード」と「日付」でグループ化します。

マスタの「日付」と「単価」を展開する。

「直近日付」=「マスタ日付」のみにする。
つまり、マスタとして必要なものは、伝票日付以前の中の最終日付の単価ということです。

「直近日付」「tblマスタ.日付」「判定2」
これらの列はもう不要なので列削除

もとの日付順にして完了

完成したコードが以下になります。

let
    ソース = Table.NestedJoin(tbl伝票, {"コード"}, tblマスタ, {"コード"}, "tblマスタ", JoinKind.LeftOuter),
    #"展開された tblマスタ" = Table.ExpandTableColumn(ソース, "tblマスタ", {"日付", "単価"}, {"tblマスタ.日付", "tblマスタ.単価"}),
    追加されたカスタム = Table.AddColumn(#"展開された tblマスタ", "判定1", each if [tblマスタ.日付]>[日付] then 1 else 0),
    フィルターされた行1 = Table.SelectRows(追加されたカスタム, each ([判定1] = 0)),
    削除された列 = Table.RemoveColumns(フィルターされた行1,{"判定1"}),
    グループ化された行 = Table.Group(削除された列, {"日付", "コード"}, {{"直近日付", each List.Max([tblマスタ.日付]), type nullable date}, {"単価等", each _, type table [日付=nullable date, コード=nullable text, tblマスタ.日付=nullable date, tblマスタ.単価=nullable number]}}),
    #"展開された 単価等" = Table.ExpandTableColumn(グループ化された行, "単価等", {"tblマスタ.日付", "tblマスタ.単価"}, {"単価等.tblマスタ.日付", "単価等.tblマスタ.単価"}),
    追加されたカスタム1 = Table.AddColumn(#"展開された 単価等", "判定2", each if [直近日付]=[単価等.tblマスタ.日付] then 0 else 1),
    フィルターされた行 = Table.SelectRows(追加されたカスタム1, each ([判定2] = 0)),
    削除された列1 = Table.RemoveColumns(フィルターされた行,{"直近日付", "単価等.tblマスタ.日付", "判定2"}),
    並べ替えられた行 = Table.Sort(削除された列1,{{"日付", Order.Ascending}})
in
    並べ替えられた行

最初の解答コードはこれを元に手動で書き換えたものになります。
ほぼ原形は無くなっていますが・・・

letの活用と式の簡略化

GUI操作で作成したクエリを元に1つのクエリとしてまとめ、さらに簡潔な記述にしていきます。
まず、別クエリのテーブルの取り込みをこのクエリの先頭に追加します。

let
    tbl伝票 = let
                    ソース = Excel.CurrentWorkbook(){[Name="tbl伝票"]}[Content],
                    変更された型 = Table.TransformColumnTypes(ソース,{{"日付", type date}, {"コード", type text}})
              in
                    変更された型,
    tblマスタ = let
                    ソース = Excel.CurrentWorkbook(){[Name="tblマスタ"]}[Content],
                    変更された型 = Table.TransformColumnTypes(ソース,{{"コード", type text}, {"日付", type date}, {"単価", Int64.Type}})
                in
                    変更された型,
                    
    ソース = Table.NestedJoin(tbl伝票, {"コード"}, tblマスタ, {"コード"}, "tblマスタ", JoinKind.LeftOuter),
    #"展開された tblマスタ" = Table.ExpandTableColumn(ソース, "tblマスタ", {"日付", "単価"}, {"tblマスタ.日付", "tblマスタ.単価"}),
    追加されたカスタム = Table.AddColumn(#"展開された tblマスタ", "判定1", each if [tblマスタ.日付]>[日付] then 1 else 0),
    フィルターされた行1 = Table.SelectRows(追加されたカスタム, each ([判定1] = 0)),
    削除された列 = Table.RemoveColumns(フィルターされた行1,{"判定1"}),
    グループ化された行 = Table.Group(削除された列, {"日付", "コード"}, {{"直近日付", each List.Max([tblマスタ.日付]), type nullable date}, {"単価等", each _, type table [日付=nullable date, コード=nullable text, tblマスタ.日付=nullable date, tblマスタ.単価=nullable number]}}),
    #"展開された 単価等" = Table.ExpandTableColumn(グループ化された行, "単価等", {"tblマスタ.日付", "tblマスタ.単価"}, {"単価等.tblマスタ.日付", "単価等.tblマスタ.単価"}),
    追加されたカスタム1 = Table.AddColumn(#"展開された 単価等", "判定2", each if [直近日付]=[単価等.tblマスタ.日付] then 0 else 1),
    フィルターされた行 = Table.SelectRows(追加されたカスタム1, each ([判定2] = 0)),
    削除された列1 = Table.RemoveColumns(フィルターされた行,{"直近日付", "単価等.tblマスタ.日付", "判定2"}),
    並べ替えられた行 = Table.Sort(削除された列1,{{"日付", Order.Ascending}})
in
    並べ替えられた行

letはネストできるので、このように部品として追加していけます。
別クエリにletで名前を付けて追加していくだけで良いです。

次に処理内容ごとに1つのletにまとめます。
この時、新しくletで入れた変数が次のステッブで使う変数になるので、その書き換えも忘れずに。

let
    tbl伝票 = let
                    ソース = Excel.CurrentWorkbook(){[Name="tbl伝票"]}[Content],
                    変更された型 = Table.TransformColumnTypes(ソース,{{"日付", type date}, {"コード", type text}})
              in
                    変更された型,
    tblマスタ = let
                    ソース = Excel.CurrentWorkbook(){[Name="tblマスタ"]}[Content],
                    変更された型 = Table.TransformColumnTypes(ソース,{{"コード", type text}, {"日付", type date}, {"単価", Int64.Type}})
                in
                    変更された型,
                    
    tbl伝票マスタ = let 
                        ソース = Table.NestedJoin(tbl伝票, {"コード"}, tblマスタ, {"コード"}, "tblマスタ", JoinKind.LeftOuter),
                        #"展開された tblマスタ" = Table.ExpandTableColumn(ソース, "tblマスタ", {"日付", "単価"}, {"tblマスタ.日付", "tblマスタ.単価"}),
                        追加されたカスタム = Table.AddColumn(#"展開された tblマスタ", "判定1", each if [tblマスタ.日付]>[日付] then 1 else 0),
                        フィルターされた行1 = Table.SelectRows(追加されたカスタム, each ([判定1] = 0)),
                        削除された列 = Table.RemoveColumns(フィルターされた行1,{"判定1"})
                    in 削除された列,
    tbl伝票単価 = let
                        グループ化された行 = Table.Group(tbl伝票マスタ, {"日付", "コード"}, {{"直近日付", each List.Max([tblマスタ.日付]), type nullable date}, {"単価等", each _, type table [日付=nullable date, コード=nullable text, tblマスタ.日付=nullable date, tblマスタ.単価=nullable number]}}),
                        #"展開された 単価等" = Table.ExpandTableColumn(グループ化された行, "単価等", {"tblマスタ.日付", "tblマスタ.単価"}, {"単価等.tblマスタ.日付", "単価等.tblマスタ.単価"}),
                        追加されたカスタム1 = Table.AddColumn(#"展開された 単価等", "判定2", each if [直近日付]=[単価等.tblマスタ.日付] then 0 else 1),
                        フィルターされた行 = Table.SelectRows(追加されたカスタム1, each ([判定2] = 0)),
                        削除された列1 = Table.RemoveColumns(フィルターされた行,{"直近日付", "単価等.tblマスタ.日付", "判定2"})
                    in 削除された列1,
    並べ替えられた行 = Table.Sort(tbl伝票単価,{{"日付", Order.Ascending}})
in
    並べ替えられた行

ここからは、各ステッブの中の無駄や記述がまとめられるものをまとめていきます。

追加されたカスタム = Table.AddColumn(#"展開された tblマスタ", "判定1", each if [tblマスタ.日付]>[日付] then 1 else 0),
フィルターされた行1 = Table.SelectRows(追加されたカスタム, each ([判定1] = 0)),

この部分は、if判定で1,0の列を追加しフィルターしていますが、
直接SelectRowsの中に判定式をいれてフィルターするようにまとめた記述に変更できます。

フィルタ = Table.SelectRows(展開, each ([tblマスタ.日付]<=[日付]))

GUI画面のフィルターでは値の選択しかできませんが、式を入れることで様々な条件でフィルターすることができます。
このようにしてまとめて、さらにコメントを付記したものが以下のコードになります。

let
    // tbl伝票の取り込みと日付型への変更
    tbl伝票 = let ソース = Excel.CurrentWorkbook(){[Name="tbl伝票"]}[Content],
                  型変換 = Table.TransformColumnTypes(ソース,{{"日付", type date}})
            　in  型変換,
    // tblマスタの取り込みと日付型への変更
    tblマスタ = let ソース = Excel.CurrentWorkbook(){[Name="tblマスタ"]}[Content],
                    型変換 = Table.TransformColumnTypes(tblマスタ,{{"日付", type date}})
                in 型変換,
    // tbl伝票とtblマスタの結合、マスタの日付が伝票日付より大きい場合行は対象外
    tbl伝票マスタ = let ソース = Table.NestedJoin(tbl伝票, {"コード"}, tblマスタ, {"コード"}, "tblマスタ", JoinKind.LeftOuter),
                        展開 = Table.ExpandTableColumn(ソース, "tblマスタ", {"日付", "単価"}, {"tblマスタ.日付", "tblマスタ.単価"}),
                        フィルタ = Table.SelectRows(展開, each ([tblマスタ.日付]<=[日付]))
                   in  フィルタ,
    // 日付とコードでグループ7化し、有効なマスタ日付の最大日に絞り込む
    tbl伝票単価 = let グループ化 = Table.Group(tbl伝票マスタ, {"日付", "コード"}, 
                                                            {{"直近日付", each List.Max([tblマスタ.日付]), type nullable date}, 
                                                             {"単価", each _, type table [単価=nullable number]}}),
                      単価列展開 = Table.ExpandTableColumn(グループ化, "単価", {"tblマスタ.日付", "tblマスタ.単価"}, {"マスタ日付", "単価"}),
                      直近単価 = Table.SelectRows(単価列展開, each ([直近日付] = [マスタ日付])),
                      作業列削除 = Table.RemoveColumns(直近単価,{"直近日付", "マスタ日付"})
                  in 作業列削除,
    // 順序を整えて出力
    並べ替え = Table.Sort(tbl伝票単価,{{"日付", Order.Ascending}})
in
    並べ替え

もちろん書き方はいろいろありますし、M言語をやっていけばさらに簡潔に記述することもできるようになります。

M言語のコメント

※Learn 字句の構造コメント

単一行コメントと区切られたコメントという2つの形式のコメントがサポートされています。

単一行コメント

// 文字で始まり、ソース行の末尾まで拡張されます。

区切られたコメント

/* 文字で始まり、*/ 文字で終わります。
複数行にまたがる場合があります。

コメントは入れ子になりません。
文字シーケンス /* と */ は単一行コメント内で特別な意味を持ちません。
また、// と /* の文字シーケンスは、区切られたコメント内で特別な意味を持ちません。
コメントは、テキストリテラル内では処理されません。

Table.NestedJoin

※Learn Table.NestedJoin

構文

Table.NestedJoin(table1 as table, key1 as any, table2 as any, key2 as any, newColumnName as text, optional joinKind as nullable number, optional keyEqualityComparers as nullable list) as table

key1 (table1) および key2 (table2) によって選択されたキー列の値が等しいかどうかに基づいて、table1 の行を table2 の行と結合します。
結果は newColumnName という名前の列に入力されます。

省略可能な joinKind によって、実行する結合の種類が指定されます。
既定では、joinKind が指定されていない場合は、左外部結合が実行されます。

キー列の比較方法を指定するために、省略可能な keyEqualityComparers のセットを含めることができます。
keyEqualityComparers 機能は現在、内部で使用することのみを目的としています。

Table.ExpandTableColumn

※Learn Table.ExpandTableColumn

構文

Table.ExpandTableColumn(table as table, column as text, columnNames as list, optional newColumnNames as nullable list) as table

table[column] 内のテーブルを複数の行と列に展開します。
columnNames は、内側のテーブルから展開する列を選択するために使用します。
既存の列と新しい列が競合しないようにするには、newColumnNames を指定します。

GUI画面で、左右に開いたこのアイコンをクリックした時に作成されるスクリブトになります。

Table.SelectRows

※Learn Table.SelectRows

構文

Table.SelectRows(table as table, condition as function) as table

選択 condition と一致する行のテーブルを table から返します。

condition as function
つまりfunctionを指定します。
解答のクエリでは、このfunctionに、
each ([tblマスタ.日付]<=[日付])
このように指定しています。
Eachについて簡単に説明します。

※Learn Power Query M 関数について

このなかで、
each キーワード
each キーワードは、単純な関数の作成に使用します。
“each ...” は、_ パラメーター "(_) => ..." を取る関数シグネチャの糖衣構文です。
each は、既定で _ に適用される lookup 演算子と組み合わせると便利です。
たとえば、each [CustomerID] は each _[CustomerID] と同じですが、これは (_) => _[CustomerID] と同じです。

説明がわかりずらいのですが、
つまり、先のEachで、
フィルタ = Table.SelectRows(展開, each ([tblマスタ.日付]<=[日付]))
これは以下のように書き換えることができます。
フィルタ = Table.SelectRows(展開, (_)=>(_[tblマスタ.日付]<=_[日付]))

Table.Group

※Learn Table.Group

構文

Table.Group(table as table, key as any, aggregatedColumns as list, optional groupKind as nullable number, optional comparer as nullable function) as table

key によって定義されたキー列によって table の行をグループ分けします。
key には、1 つの列名または列名のリストを指定できます。

各グループについて、aggregatedColumns によって指定された集計列と共に、キー列 (およびその値) を含むレコードが作成されます。
必要に応じて、groupKind と comparer を指定することもできます。

データがキー列によって既に並べ替え済みの場合は、GroupKind.Local の groupKind を指定できます。
これにより、特定のキー値のセットを持つすべての行が連続すると見なされるので、特定の場合にグループ化のパフォーマンスが向上する可能性があります。

comparer を渡すとき、異なるキーが同等に処理される場合は、キーが自身のキーとは異なるグループに行が配置される可能性があることに注意してください。

この関数では、返される行の順序は保証されません。

解答スクリブトでの指定は、
Table.Group(フィルター行1, {"日付", "コード"}, {{"直近日付", each List.Max([tblマスタ.日付]), type nullable date}, {"単価", each _, type table [単価=nullable number]}}),

keyに

列名リスト {"日付", "コード"}

aggregatedColumnsに、

{"直近日付", each List.Max([tblマスタ.日付]), type nullable date},
{"単価", each _, type table [単価=nullable number]}
1つのリスト内に、この2列を指定したリストを入れています。

Table.Sort

※Learn Table.Sort

構文

Table.Sort(table as table, comparisonCriteria as any) as table

1 つ以上の列名と省略可能な comparisonCriteria のリストを { { col1, comparisonCriteria }, {col2} } の形式で使用して、table を並べ替えます。
解答スクリブトでの指定は、
Table.Sort(削除された列,{{"日付", Order.Ascending}})
「日付」列で集順に並べています。

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

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

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

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

ホーム
その他
PowerQuery（M言語）入門
有効な最新単価の取得

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

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

Power Query（M言語）入門
有効な最新単価の取得