PowerQueryで一つ上の行を計算させたい

Question

前提

PowerQueryで一つ上の行を計算をさせる方法を知りたいです。

実現したいこと

サンプルデータとして、以下のようなデータがあったとします。

日付	月初の在庫	販売量
10/1	100
10/5	-	10
10/20	-	5

それを、月初の在庫から引き算を順繰りに行い、以下のような「現在の在庫数」という列を追加したい。

日付	月初の在庫	販売量	現在の在庫数
10/1	100
10/5	-	10	90
10/20	-	5	80

試したこと

インデックス列を追加をして、その番号を起点に以下のような数式を書いてみたのですが、
10/5の時点のデータは表示されましたが、後ろに続く数量がnullで出てしまいました。
nullではなく、想定の上記のような80を出したいのですが、どなたかご教示いただけないでしょうか。

試した式と、出力結果イメージは以下です。

カスタム=
if DATA[月初の在庫]<>null
then DATA[月初の在庫]{[インデックス]-1}-[販売量] 
else DATA[現在の在庫数]{[インデックス]-1}-[販売量]

日付	月初の在庫	販売量	インデックス	現在の在庫数
10/1	100		0	error
10/5	null	10	1	90
10/20	null	5	2	null

最初の行のerrorは、最後に0として置き換えをしようとしています。

Comments

[mit233]

出したい結果は90-5なので、85になります。
失礼いたしました。

Answer 1

let
    //テーブルの参照
    Source = Excel.CurrentWorkbook(){[Name="テーブル名"]}[Content],
    //各列の型定義
    ChangedType = Table.TransformColumnTypes(Source,{{"日付", type date}, {"月初の在庫", Int64.Type}, {"販売量", Int64.Type}}),
    //インデックス列の追加
    AddedIndex = Table.AddIndexColumn(ChangedType, "インデックス", 1, 1, Int64.Type),
    //行ごとに[月初の在庫]列の累計値を返すカスタム列[入庫数の累計]の追加
    AddedCumulativeInventories = Table.AddColumn(AddedIndex, "入庫数の累計", each List.Sum(List.FirstN(AddedIndex[月初の在庫],[インデックス]))),
    //行ごとに[販売量]列の累計値を返すカスタム列[出庫数の累計]を追加
    AddedCumulativeSales = Table.AddColumn(AddedCumulativeInventories, "出庫数の累計", each List.Sum(List.FirstN(AddedIndex[販売量],[インデックス]))),
    //行ごとに[入庫数の累計]と[出庫数の累計]の差を返すカスタム列[現在の在庫数]の追加
    AddedCurrentInventories = Table.AddColumn(AddedCumulativeSales, "現在の在庫数", each [入庫数の累計] - [出庫数の累計]),
    //列の並べ替え
    ReorderedColumns = Table.ReorderColumns(AddedCurrentInventories,{"インデックス", "日付", "月初の在庫", "販売量", "入庫数の累計", "出庫数の累計", "現在の在庫数"})
in
    ReorderedColumns

こんな感じのクエリを作成なさればよろしいのではないかと。

Comments

[mit233]

ありがとうございます。累計にしてしまうことで解決ができました。やりたかったことも解決しました！
お知恵ありがとうございました。