【Power Query】Table.Pivotの月の列は固定リストで渡す

毎月届くCSVから、直近3か月を横に並べて比べる処理があります。ところが、CSVが1か月分しかない月に実行すると、前月の列が無いため、後ろのステップがエラーで止まります。

そこで前月の列を0で埋めると、今度は1か月分しかないのに全員が「新規」に見えます。

この記事では、Table.Pivotに月のリストを固定で渡し、無い月をnullの列として残す書き方を紹介します。【Power Query】年月ごとにCSVの最新版だけを採用するの続きです。

存在する月だけを列にすると止まる
列リストを固定で渡す
「前月なし」は、列ではなくデータで判定する
0で埋めると「新規」が崩れる
1〜4か月分と、1か月抜けで確認した結果
まとめ

存在する月だけを列にすると止まる

データの最新の年月を当月（M0）、前月をM1、前々月をM2とします。M0〜M2を列にして、当月と前月を比べます。

ピボットの列を、データにある月だけにすると、1か月分のCSVではM1の列ができません。その状態で[M1]を参照すると、次のエラーで止まりました。

レコードのフィールド 'M1' が見つかりませんでした。

列リストを固定で渡す

前の記事の最後のステップ（Result）の次から、次のように続けます。

MonthNo = (ym as text) as number => Number.FromText(Text.Start(ym, 4), "en-US") * 12 + Number.FromText(Text.End(ym, 2), "en-US"),
LatestNo = List.Max(List.Transform(Result[YearMonth], MonthNo)),
WithKey = Table.AddColumn(Result, "Key", each "M" & Text.From(LatestNo - MonthNo([YearMonth]), "en-US"), type text),
Wanted = {"M0", "M1", "M2"},
Recent = Table.SelectRows(WithKey, each List.Contains(Wanted, [Key])),
Slim = Table.SelectColumns(Recent, {"Person", "Item", "Key", "Amount"}),
Wide = Table.Pivot(Slim, Wanted, "Key", "Amount", List.Sum),
HasPrev = List.Contains(Slim[Key], "M1"),
Final = Table.AddColumn(Wide, "Status", each
    if not HasPrev then "前月なし"
    else if [M0] = null and [M1] = null then null
    else if [M1] = null then "新規"
    else if [M0] = null then "当月なし"
    else if [M0] > [M1] then "増加"
    else if [M0] < [M1] then "減少"
    else "変化なし", type text)

ポイントは、Table.Pivotの第2引数に、固定のWantedを渡す1行です。

月差を「M0」のような文字列のキーにします。ピボットの列見出しになるためです。データの最新月が基準なので、今日の日付には左右されません。
固定リストに入れた月のうち、データに無い月は、nullの列になりました。CSVが1か月分なら、M1とM2は全員nullです。
列は、M0、M1、M2の順に並びました。並べ替えの処理は要りませんでした。
存在する月だけをピボットして、無い月をTable.AddColumnで足す書き方でも、結果は同じでした。固定リストなら1行で済みます。

公式の関数リファレンスは、Table.Pivot – PowerQuery M | Microsoft Learnにあります。

「前月なし」は、列ではなくデータで判定する

固定リストにするとM1の列は常にあるので、列の有無では前月のデータがあるか分かりません。そこで、HasPrevでデータにM1が含まれるかを見ます。

前月のデータが無いときは、全員「前月なし」です。
前月のデータがあるときは、前月に行が無い人（null）を「新規」にします。前月に実際の0円がある人（0）は、金額の比較で判定します。

0で埋めると「新規」が崩れる

無い月を0で埋めると、前月に行が無いことと、前月が0円だったことが区別できなくなります。2か月分のデータで、左がnullのまま、右が0で埋めた結果です。

2か月分のデータをピボットした結果の表。左は無い月をnullのままにした表、右は0で埋めた表で、前月0円のMoriが右では「新規」になっている — 再現用の最小コードの実行結果（左がnullのまま、右が0で埋めた表）

前月が0円のMoriは、nullの設計では「増加」ですが、0で埋めると「新規」になります。

状況	nullの設計	0で埋める
1か月分のCSV（5人）	全員「前月なし」	全員「新規」
2か月分で、前月に0円の人	「増加」	「新規」
3か月分で、当月も前月も行が無い人	状態は空欄	「変化なし」

空欄と0の違いは、【Excel関数】率の平均はAVERAGEでなくSUM/SUMでも扱っています。