1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

ある時系列順に並んだデータから、1行上の値と比較を行うためのクエリを考えます。
下記の「Power BI Community Blog」記事を参考にしています。

入力データ

入力データはワークシート内で ROW() を使って5000行のデータを用意して、クエリで読み込んでおきます。
image.png

テーブル1
let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"values", Int64.Type}})
in
    変更された型

image.png

インデックスメソッド

オーソドックスな書き方をすると、インデックス列を用いて{}演算子で一つ前の行を読み出す方法となりますが、この方法を行うとPowerQueryではO(n^2)ぐらいの時間がかかってしまうため、行数が1000を超えるような場合ではおすすめ出来ません。

Table-Index method
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
    追加されたカスタム

image.png

マージメソッド

インデックス列を0始まりZeroIndexと1始まりOneIndexで2列作成して、それらをKeyに左結合Table.NestedJoineして作成する方法。
処理時間はインデックスメソッドよりかなり改善されて数万行でも十分処理できます。

Table-NestedJoin method
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
    並べ替えられた行

image.png

リストメソッド

テーブルの各列を一旦リスト型に変換したあと、List.RemoveLastNによってラスト1行を削除した列を作成して、再びテーブル形式に戻す方法。
マージメソッドよりも若干高速に処理されますが、M式の大部分を詳細エディタから手打ちして作成する必要があります。
元ネタの「Power BI Community Blog」のlet式は3行ほどで書かれていましたが、分かりやすくするためにステップを分割して作成したクエリが下記になります。この手法ではIndex列は不要ですが、最終結果を他と合わせるために追加しています。

List-RemoveLastN method
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

image.png
image.png
image.png
image.png

1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?