ある時系列順に並んだデータから、1行上の値と比較を行うためのクエリを考えます。
下記の「Power BI Community Blog」記事を参考にしています。
入力データ
入力データはワークシート内で ROW()
を使って5000行のデータを用意して、クエリで読み込んでおきます。
let
ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
変更された型 = Table.TransformColumnTypes(ソース,{{"values", Int64.Type}})
in
変更された型
インデックスメソッド
オーソドックスな書き方をすると、インデックス列を用いて{}
演算子で一つ前の行を読み出す方法となりますが、この方法を行うとPowerQueryではO(n^2)ぐらいの時間がかかってしまうため、行数が1000を超えるような場合ではおすすめ出来ません。
let
Source = テーブル1,
ZeroIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
追加されたカスタム = Table.AddColumn(ZeroIndex, "prev.values", each try ZeroIndex{[Index]-1}[values] otherwise null)
in
追加されたカスタム
マージメソッド
インデックス列を0始まりZeroIndex
と1始まりOneIndex
で2列作成して、それらをKeyに左結合Table.NestedJoine
して作成する方法。
処理時間はインデックスメソッドよりかなり改善されて数万行でも十分処理できます。
let
Source = テーブル1,
ZeroIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
OneIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
マージされたクエリ数 = Table.NestedJoin(ZeroIndex, {"Index"}, OneIndex, {"Index"}, "One", JoinKind.LeftOuter),
#"展開された One" = Table.ExpandTableColumn(マージされたクエリ数, "One", {"values"}, {"prev.values"}),
並べ替えられた行 = Table.Sort(#"展開された One",{{"Index", Order.Ascending}})
in
並べ替えられた行
リストメソッド
テーブルの各列を一旦リスト型に変換したあと、List.RemoveLastN
によってラスト1行を削除した列を作成して、再びテーブル形式に戻す方法。
マージメソッドよりも若干高速に処理されますが、M式の大部分を詳細エディタから手打ちして作成する必要があります。
元ネタの「Power BI Community Blog」のlet式は3行ほどで書かれていましたが、分かりやすくするためにステップを分割して作成したクエリが下記になります。この手法ではIndex列は不要ですが、最終結果を他と合わせるために追加しています。
let
Source = テーブル1,
ZeroIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
toColumns = Table.ToColumns(ZeroIndex),
prevValues = {{null} & List.RemoveLastN(ZeroIndex[values],1)},
columnNames = Table.ColumnNames(ZeroIndex)&{"prev.values"},
Custom1 = Table.FromColumns(toColumns & prevValues,columnNames)
in
Custom1