Power Queryで移動平均

4714 ワード
PowerBI DAX Excel PowerQuery Excel テキストリンク

2種類書いてみたものの、いずれにしても時間がかかる印象です。
DAXが書けるようになったら、比べてみたい。

※当方PCの環境は、CPUがCeleron G530、メモリ4GBで、Win7(64bit)、365のExcel(32bit)です。

2019/10/2修正:コード内のCustom1にて、誤ってList.Sum関数を使っていたので、List.Average関数に修正しました。

サンプルデータ

詳細エディタに貼ると、テーブルになります。関数で乱数を発生させてます。
2000/1/1から2019/9/1までの時間別データで約18万行です。

t_sample
let
    Source = List.Generate(
                ()=>#datetime(2000,1,1,0,0,0),
                each _<=#datetime(2019,9,1,0,0,0),
                each _+#duration(0,1,0,0)
             ),
    Custom1 = List.Transform(
                List.Random(List.Count(Source),0),
                each Number.Round(_*100,0)//整数にしました。
              ),
    Custom2 = Table.FromColumns({Source,Custom1}),
    #"Changed Type" = Table.TransformColumnTypes(Custom2,{
                        {"Column1", type datetime}, {"Column2", Int64.Type}}
                      ),
    Custom3 = #"Changed Type"
in
    Custom3

テーブルの様子

コード

24時間移動平均をやってます。「その2」の方が速い。
なお、いずれもList.Bufferを入れたので、Bufferしきれないほど多量のデータの場合は、Bufferをはずして、地道に更新されるのを待つことになります。
※Bufferしきれない場合は、エラー？メッセージが出ます。

その1～List.Range関数で絞りこむ

ロードに5～6分。

let
    Source = List.Buffer(t_sample[Column2]),
    id = List.Positions(Source),
    間隔 = 24,
    Custom1 = List.Transform(id,each 
                if _<間隔-1 then null
                else List.Average(
                        List.Range(Source,_-(間隔-1),間隔)
                     )
              ),
    Custom2 = Table.FromColumns(
                Table.ToColumns(t_sample) & {Custom1},
                Value.Type(
                    Table.AddColumn(t_sample,"移動平均",each null,Int64.Type)
                )
            　)
in
    Custom2

その2～List.SkipとList.FirtstNのあわせ技

対象となるデータを減らしたことになるのか、ロード時間は2分半に半減。

let
    Source = List.Buffer(t_sample[Column2]),
    id = List.Positions(Source),
    間隔 = 24,
    Custom1 = List.Transform(id,each
                if _<間隔-1 then null
                else List.Average(
                        //リストの生成方法を変更
                        List.FirstN(
                            List.Skip(Source,_-(間隔-1)),
                            間隔
                        )
                     )
              ),
    //ここはList.Range()版と同様。
    Custom2 = Table.FromColumns(Table.ToColumns(t_sample) & {Custom1},
                Value.Type(
                    Table.AddColumn(t_sample,"移動平均",each null,Int64.Type)
                )
              )
in
    Custom2

おまけ

欲張らずに7000行の3日平均で比較してみたところ、
　その1　⇒2分超
　その2　⇒3秒
という結果でした。　

約7000行の日別データを生成するコード

t_sample2
let
    Source = List.Generate(
                ()=>#datetime(2000,1,1,0,0,0),
                each _<=#datetime(2019,9,1,0,0,0),
                each _+#duration(1,0,0,0)
             ),
    Custom1 = List.Transform(
                List.Random(List.Count(Source),0),
                each Number.Round(_*100,0)//整数にしました。
              ),
    Custom2 = Table.FromColumns({Source,Custom1}),
    #"Changed Type" = Table.TransformColumnTypes(Custom2,{
                        {"Column1", type datetime}, {"Column2", Int64.Type}}
                      ),
    Custom3 = #"Changed Type"
in
    Custom3

リンク

List.Range - PowerQuery M | Microsoft Docs
List.FirstN - PowerQuery M | Microsoft Docs
List.Skip - PowerQuery M | Microsoft Docs