Forum Discussion

WSCTech's avatar
WSCTech
Frequent Visitor
2 years ago
Solved

Running Max with Flag in Power Query M

Dear Power Query experts,   I have time series data, with 1 second intervals, spanning multiple days.   It's thousands of rows, so I would like to achieve this in Power Query M, before the data i...
Need Help
  • jgeddes's avatar
    jgeddes
    2 years ago

    I was able to get your desired result with the following...

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc67CsMwDAXQXymaE9DDdppshVLo3s1kMMSEQtOpQz6/jgKaNAjE1UFSzvD8LnWHDu7lV1/vrbb2dtRW9senrDB3GbAFjCw9YS90wThJmpBaSnjMSBW5ilWRKXaVqGJT4qpgu1BVcFVs6Tgaii5KdvBUyVWDKrG3BlddVYVTzX8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).3" = _t, #"(blank).4" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"DateTime", type datetime}, {"A", type number}, {"Index", Int64.Type}}),
    SortedTable = Table.Sort(#"Changed Type", {"Index", Order.Ascending}),
    AddMaxA = 
    Table.AddColumn(
        SortedTable, 
        "_maxFlagA", 
        each 
            let
            currentA = [A],
            currentRow = [Index],
            precedingRows = Table.SelectRows(SortedTable, each [Index] <= currentRow),
            runningMax = List.Max(precedingRows[A]),
            firstIndex = List.Min(Table.SelectRows(SortedTable, each [A] = runningMax)[Index])
        in
            if currentA = runningMax and currentRow = firstIndex then 1 else 0
    )
in
    AddMaxA

     It hopefully gets you pointed in the right direction.

jgeddes's avatar
jgeddes
Super User
2 years ago

I was able to get your desired result with the following...

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc67CsMwDAXQXymaE9DDdppshVLo3s1kMMSEQtOpQz6/jgKaNAjE1UFSzvD8LnWHDu7lV1/vrbb2dtRW9senrDB3GbAFjCw9YS90wThJmpBaSnjMSBW5ilWRKXaVqGJT4qpgu1BVcFVs6Tgaii5KdvBUyVWDKrG3BlddVYVTzX8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).3" = _t, #"(blank).4" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"DateTime", type datetime}, {"A", type number}, {"Index", Int64.Type}}),
    SortedTable = Table.Sort(#"Changed Type", {"Index", Order.Ascending}),
    AddMaxA = 
    Table.AddColumn(
        SortedTable, 
        "_maxFlagA", 
        each 
            let
            currentA = [A],
            currentRow = [Index],
            precedingRows = Table.SelectRows(SortedTable, each [Index] <= currentRow),
            runningMax = List.Max(precedingRows[A]),
            firstIndex = List.Min(Table.SelectRows(SortedTable, each [A] = runningMax)[Index])
        in
            if currentA = runningMax and currentRow = firstIndex then 1 else 0
    )
in
    AddMaxA

 It hopefully gets you pointed in the right direction.

WSCTech's avatar
WSCTech
Frequent Visitor
2 years ago

Hi jgeddes Thank you for responding to this and for your help.

When I first applied your solution to my dataset, it took a bit for the preview to load, but I saw that it was working. The numbers in the preview window looked correct. However, I have clicked "Close & Apply" and now waited over an hour and the status shows "32.6 GB from mycsv" loaded so far...   I'm guessing it's looping through all the data for each element of the data. My input file was only about 15MB or about 46k rows, but I might need to apply this to even bigger data sets.

 

I'll mark this as solved, but I think I'll just have to go back to good 'ol Python for this one. 

 

But if you or anyone else have a more optimized way of doing it in PowerBI, please feel free to share.

 

Thanks again!