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

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

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言語…
有効な最新単価の取得
10本目 「tbl伝票」(日付,コード) 「tblマスタ」(コード,日付,単価) マスタの日付は(これ以降有効となる)適用開始日です。「tbl伝票」に単価を付して出力してください。※例.2/5のA01はマスタの2/1が適用されます。


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