Power Query(M言語)入門
グルーブ内の最小・最大

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

グルーブ内の最小・最大


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


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

パワク9本目

Excel エクセル Power Query M言語

#パワク9本目
ブック内に日付と商品コードを持つテーブル「tbl伝票」があります。
商品コード毎の日付の最小値と最大値(最古日付と最新日付)を出力してください。
※サンブルデータはALT
#PowerQuery #M言語


解答コード

Excel エクセル Power Query M言語
let
    ソース = Excel.CurrentWorkbook(){[Name="tbl伝票"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"日付", type date}, {"商品コード", type text}}),
    グループ化された行 = Table.Group(変更された型, {"商品コード"}, 
                        {{"最小日", each List.Min([日付]), type nullable date}, {"最大日", each List.Max([日付]), type nullable date}})
in
    グループ化された行
GUI操作で作成されたままです。(改行だけしました。)
新しく出てきたものは、
Table.Group
これについては後述します。


GUI操作での作成

対象のテーブルないのセルを選択し、

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言語

これで「OK」

Excel エクセル Power Query M言語

これで作成されたクエリが以下になります。
let
    ソース = Excel.CurrentWorkbook(){[Name="tbl伝票"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"日付", type date}, {"商品コード", type text}}),
    グループ化された行 = Table.Group(変更された型, {"商品コード"}, {{"最小", each List.Min([日付]), type nullable date}, {"最大", each List.Max([日付]), type nullable date}})
in
    グループ化された行


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

この関数では、返される行の順序は保証されません。
解答スクリブトでの指定は、
key as any
"商品コード"
aggregatedColumns as list
{{"最小日", each List.Min([日付]), type nullable date}, {"最大日", each List.Max([日付]), type nullable date}}
2つのリストを1つのリストにして指定しています。
{"最小日", each List.Min([日付]), type nullable date}
{"最大日", each List.Max([日付]), type nullable date}


List.Min/List.Max

List.Min(list as list, optional default as any, optional comparisonCriteria as any, optional includeNulls as nullable logical) as any
List.Max(list as list, optional default as any, optional comparisonCriteria as any, optional includeNulls as nullable logical) as any

リスト list 内の最小/最大の項目が返されます。
リストが空の場合は、省略可能な既定値 default が返されます。
省略可能な comparisonCriteria 値の comparisonCriteria を指定すると、リスト内の項目を比較する方法を指定できます。
このパラメーターが null の場合、既定の比較関数が使用されます。




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

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


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

テンキーのスクリーンキーボード作成|ユーザーフォーム入門(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)
スピらない スピル数式 スピらせる|エクセル雑感(2023-12-06)
イータ縮小ラムダ(eta reduced lambda)|エクセル入門(2023-11-20)


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

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」をお願いいたします。
本文下部へ