【PowerQuery】一つ前の行のデータを利用したい

パワークエリ１年生の苦悩シリーズ。

エクセル関数・VBA使いが、パワークエリをやってみて、文化の違いに苦戦しつつ七転八倒の末なんとか中の下レベルまではマスター。（したと思っている）

一つ前の行と比べて一致するとほにゃららとか、差を出してほにゃららとか、ワークシート上ではいとも簡単にできるのに、パワークエリではどうやってやるんだ。。。と詰まったので、ここに研究結果を記します。

１．同じテーブルをマージして一つ前の行のデータを取得する方法
２．前のステップのテーブルを使う方法
３．グループごとに差分を出したい場合
- （１）テーブルをマージする方法でやってみる
- （２）リストを使用する方法
４．最後に
５．参考文献、記事

１．同じテーブルをマージして一つ前の行のデータを取得する方法

こちらのサイトで勉強させていただきました。
Power Query にて１行上の値と比較するための列を作成する

データ量が多い場合は、このやり方がいいらしい。accessでもインデックス振って、テーブル自己結合でやったりする。

テーブルを読み込んでやってみたいと思います。前日の実績との差分を出してみます。

（１）手順

①テーブルを読み込みます。

②日付の列を「日付型」に変更します。

③念のため、「日付」列を昇順で並べ替えします。

④インデックスを0始まりで追加します。

⑤ここでテーブル1のクエリを「複製」します。
複製したクエリの名前を変更し「テーブル1_2」としました。

⑥複製した「テーブル1_2」のインデックスを1始まりに変更します。

⑦テーブル1のクエリに戻り、テーブル1_2のクエリをマージします。
結合列は「インデックス」、結合の種類はそのまま「左外部・・・」とします。

⑧結合したテーブルを展開します。
ここではいったん「テーブル1_2」のすべての列を展開しまてみました。

⑨インデックス列、日付列は不要なので列を削除し、マージした前日実績は「前日実績」と列名を変更しました。

⑩カスタム列で引き算して、「実績」と「前日実績」との差分を算出しました。

（２）詳細エディタよりコード

テーブル1

let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"日付", type date}, {"実績", Int64.Type}}),
    並べ替えられた行 = Table.Sort(変更された型,{{"日付", Order.Ascending}}),
    追加されたインデックス = Table.AddIndexColumn(並べ替えられた行, "インデックス", 0, 1, Int64.Type),
    マージされたクエリ数 = Table.NestedJoin(追加されたインデックス, {"インデックス"}, テーブル1_2, {"インデックス"}, "テーブル1_2", JoinKind.LeftOuter),
    #"展開された テーブル1_2" = Table.ExpandTableColumn(マージされたクエリ数, "テーブル1_2", {"実績"}, {"前日実績"}),
    インデックス列削除 = Table.RemoveColumns(#"展開された テーブル1_2",{"インデックス"}),
    追加されたカスタム = Table.AddColumn(インデックス列削除, "前日実績との差分", each [実績]-[前日実績])
in
    追加されたカスタム

テーブル1_2

let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"日付", type date}, {"実績", Int64.Type}}),
    並べ替えられた行 = Table.Sort(変更された型,{{"日付", Order.Ascending}}),
    追加されたインデックス = Table.AddIndexColumn(並べ替えられた行, "インデックス", 1, 1, Int64.Type)
in
    追加されたインデックス

（３）別解

テーブルを複製せず、いっぺんにやることもできます。
（２）のコードをこちょこちょいじって作成したクエリが以下のコードです。同じ結果となります。

let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"日付", type date}, {"実績", Int64.Type}}),
    並べ替えられた行 = Table.Sort(変更された型,{{"日付", Order.Ascending}}),
    //0始まりインデックスのテーブルを作成＝＞tb1
    tb1 = Table.AddIndexColumn(並べ替えられた行, "インデックス", 0, 1, Int64.Type),
    //1始まりインデックスのテーブルを作成＝＞tb2
    tb2 = Table.AddIndexColumn(並べ替えられた行, "インデックス", 1, 1, Int64.Type),
    //tb1とtb2をインデックスで結合してマージ
    マージ = Table.NestedJoin(tb1, {"インデックス"}, tb2, {"インデックス"}, "tb2", JoinKind.LeftOuter),
    展開 = Table.ExpandTableColumn(マージ, "tb2", {"実績"}, {"前日実績"}),
    インデックス列削 = Table.RemoveColumns(展開,{"インデックス"}),
    差分の列追加 = Table.AddColumn(インデックス列削, "前日実績との差分", each [実績]-[前日実績])
in
    差分の列追加

並べ替えられた行をもとに０始まり、1始まりのテーブルをそれぞれ作成し、tb1、tb2として出力。
tb1とtb2を結合します。

２．前のステップのテーブルを使う方法

こちらのサイトで勉強させていただきました。
PowerQuery 1行上のセルを参照する（前月比・差分計算）
M言語に慣れる_11回目_前行を参照して計算
 PowerQuery 1行上のデータと比較する

同じテーブルでやってみます。

（１）手順

①テーブルを読み込みます。
②日付の列を「日付型」に変更します。
③念のため、「日付」列を昇順で並べ替えします。
④インデックスを0始まりで追加します。
ここまでは前項と同じです。

ここで、ポイントです。
ポイント１：直近のステップ名は「追加されたインデックス」です。
このテーブルを利用して前日実績を取り出していきます。
ポイント２：インデックスは0始まりとします

⑤カスタム列で以下の式を入力します。

= 追加されたインデックス[実績]{[インデックス]-1}

ポイントは
[実績]{[インデックス]-1}
とするのではなく、
追加されたインデックス[実績]{[インデックス]-1}
とするところです。
（追加されたインデックスは直前のステップ名です）

⑥1行目がエラーになるので、try otherwise でエラー回避して、前日実績を出します。

カスタム列の式を以下のコードで書き換えます。

try 追加されたインデックス[実績]{[インデックス]-1} otherwise null

⑦インデックス列を削除します。

⑧前日実績と実績の差分を計算する列を追加します。

（２）詳細エディターよりコード全文

let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"日付", type date}, {"実績", Int64.Type}}),
    並べ替えられた行 = Table.Sort(変更された型,{{"日付", Order.Ascending}}),
    追加されたインデックス = Table.AddIndexColumn(並べ替えられた行, "インデックス", 0, 1, Int64.Type),
    追加されたカスタム = Table.AddColumn(追加されたインデックス, "前日実績", each try 追加されたインデックス[実績]{[インデックス]-1} otherwise null),
    削除された列 = Table.RemoveColumns(追加されたカスタム,{"インデックス"}),
    追加されたカスタム1 = Table.AddColumn(削除された列, "前日実績との差分", each [実績]-[前日実績])
in
    追加されたカスタム1

（３）いったいどうゆうことが行われているのか

いわれたとおりにやったらできたけど。。。一体どうなってるのかがわからなかった。けれども分解してみたら、わかった。
これはリストを利用しているんですね。

ちょっといじりながら見ていきます。

クエリを「追加されたインデックス」ステップまで戻します。

カスタム列で
追加されたインデックス[実績]
と打ってみます。

すると、追加された列には各行に List と表示されます。

Listをクリックして中身を見ると、[実績]列の中身が入ったListであることがわかります。

ここで試しに
追加されたインデックス[実績]{0}
と式の内容を書き換えてみます。

すると、すべての行に[実績]列の一番上の値 2674 が表示されました。

追加されたインデックス[実績]{1}　とすると、2番目の値 1559 が表示されます。

ここまでくると、なんとなく下記の式の意味が分かってきましたね。

追加されたインデックス[実績]{[インデックス]-1}

追加されたインデックス[実績] でそれぞれの行にそれぞれListを持ち、Listから自分の行のインデックスから1減算したリストの値を持ってくる。

ということをしているんですね。

Listは一番最初が 0 なので、1番目の値を取り出したいときは　リスト{0} とします。

そのために、クエリで追加するインデックスは0始まりにしないと具合が悪いんです。

上記の式の {[インデックス]-1} の列名の前には {追加されたインデックス[インデックス]-1} 等とステップ名はつけません。[インデックス]はそのまま自分の行の値をとってくる必要があるからです。

カスタム列で each 追加されたインデックス[実績]、 each [インデックス] -1 とやってみたりして、あれこれいじってみると、なんとなくイメージがつかめます。

ちなみにリストに変換するときは、列を指定して　リボン「変換」→「リストへ変換」をクリックします。

数式バーをみてみると追加されたインデックス[実績] となっていますね。この「リストに変換」を each で各行で行っているんです。

大量データの場合、1の方法と比べて2の方法のほうは時間がかかる、と書いてある記事を見ましたが、たしかに負荷はかかりそうですね。

（４）別解

リスト化する部分を切り出して、以下のようにやっても同じでした。こちらのほうが理解しやすいかもしれませんね。

let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"日付", type date}, {"実績", Int64.Type}}),
    並べ替えられた行 = Table.Sort(変更された型,{{"日付", Order.Ascending}}),
    追加されたインデックス = Table.AddIndexColumn(並べ替えられた行, "インデックス", 0, 1, Int64.Type),
    //実績をリストに変換
    リスト = 追加されたインデックス[実績],
　　//リストを使用して一つ前の行の実績を取得
    追加されたカスタム = Table.AddColumn(追加されたインデックス, "前日実績", each try リスト{[インデックス]-1} otherwise null),
    削除された列 = Table.RemoveColumns(追加されたカスタム,{"インデックス"}),
    追加されたカスタム1 = Table.AddColumn(削除された列, "前日との差分", each [実績]-[前日実績])
in
    追加されたカスタム1

３．グループごとに差分を出したい場合

下図のように、A支店、B支店など店舗が異なる場合、同一店舗は前の行を参照したいが、店舗が違う場合は前の行を参照したくありません。

（１）テーブルをマージする方法でやってみる

グループ化してからインデックスを振る方法もあるのですが、あまり好きではないので並べ替えして前の行の店舗と違ったら前月実績をnullにする、という方法でやっています。

let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"年月", type date}, {"実績", Int64.Type}}),
    //支店、年月で並べ替え
    並べ替えられた行 = Table.Sort(変更された型,{{"店舗", Order.Ascending},{"年月", Order.Ascending}}),
    //インデックス付与したテーブルを2つ作成
    t1 = Table.AddIndexColumn(並べ替えられた行, "インデックス", 0, 1, Int64.Type),
    t2 = Table.AddIndexColumn(並べ替えられた行, "インデックス", 1, 1, Int64.Type),
    //t1とt2をマージ
    マージされたクエリ数 = Table.NestedJoin(t1, {"インデックス"}, t2, {"インデックス"}, "テーブル2", JoinKind.LeftOuter),
    展開 = Table.ExpandTableColumn(マージされたクエリ数, "テーブル2", {"店舗", "実績"}, {"店舗.1", "実績.1"}),
    //店舗が前の行と同じ場合、前月実績を表示
    前月実績列追加 = Table.AddColumn(展開, "前月実績", each if [店舗] = [店舗.1] then [実績.1] else null),
    削除された列 = Table.RemoveColumns(前月実績列追加,{"インデックス", "店舗.1", "実績.1"}),
    差分出力 = Table.AddColumn(削除された列, "差分", each [実績]-[前月実績])
in
    差分出力

（２）リストを使用する方法

こちらもやはり、グループ化してからインデックスを振る方法ではなく、店舗の列と実績の列をリスト化し、前の行の店舗と一致するかどうかを判定してから前の行の実績を表示する、という方法でやっています。

let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"年月", type date}, {"実績", Int64.Type}}),
    //支店、年月で並べ替え
    並べ替えられた行 = Table.Sort(変更された型,{{"店舗", Order.Ascending},{"年月", Order.Ascending}}),
    追加されたインデックス = Table.AddIndexColumn(並べ替えられた行, "インデックス", 0, 1, Int64.Type),
    //店舗リストと実績リストを作成
    店舗リスト = 追加されたインデックス[店舗],
    実績リスト = 追加されたインデックス[実績],
    //「店舗リスト」を使用し、前の行の店舗を取得
    //店舗が前の行と一致する場合1、不一致またはエラーとなる場合は0を表示
    前の行の店舗一致判定 = Table.AddColumn(追加されたインデックス,"店舗一致判定",each 
                            try 
                                if [店舗] = 店舗リスト{[インデックス]-1} then 1 else 0
                            otherwise 0
                                ),
    //店舗一致判定が1の場合、「実績リスト」から前の行の実績を表示
    追加されたカスタム = Table.AddColumn(前の行の店舗一致判定, "前月実績", each if [店舗一致判定] = 1 then 実績リスト{[インデックス]-1} else null),
    削除された列 = Table.RemoveColumns(追加されたカスタム,{"インデックス", "店舗一致判定"}),
    追加されたカスタム1 = Table.AddColumn(削除された列, "差分", each [実績]-[前月実績]) 
in
    追加されたカスタム1