グルーブ内の最小・最大｜Power Query（M言語）入門

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

グルーブ内の最小・最大

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

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

ページ内目次

パワク9本目
解答コード
GUI操作での作成
Table.Group
List.Min/List.Max

パワク9本目

#パワク9本目
ブック内に日付と商品コードを持つテーブル「tbl伝票」があります。
商品コード毎の日付の最小値と最大値（最古日付と最新日付）を出力してください。
※サンブルデータはALT
#PowerQuery #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操作での作成

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

「日付」は時刻不要なので型変更します。

「現在のものを変更」

次に「グループ化」をクリック

「詳細設定」を選択

「詳細設定」を選択することで、「集計の追加」が表示されるようになります。

グルー化のキーとして「商品コード」
新しい列名に「最小値」、操作に「最小」、列に「日付」
新しい列名に「最大値」、操作に「最大」、列に「日付」
（列名は任意です）

これで「OK」

これで作成されたクエリが以下になります。

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

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

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

解答スクリブトでの指定は、
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

※Learn List.Min
※Learn 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言語）入門」の記事

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

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伝票…

有効な最新単価の取得

10本目「tbl伝票」（日付,コード）「tblマスタ」（コード,日付,単価）マスタの日付は（これ以降有効となる）適用開始日です。「tbl伝票」に単価を付して出力してください。※例.2/5のA01はマスタの2/1が適用されます。

新旧マスタの差異比較

11本目 2つのテーブル「TBL新」「TBL旧」統合して差異の区分を追加してください。TBL旧のみ："削除" TBL新のみ："新規" 両方で内容違い："変更"（名称等はTBL新を出力）新旧同じ：空欄 ※サンブルデータはALT #PowerQuery#M言語…

有効な最新単価の取得

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

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言語）入門
グルーブ内の最小・最大