Running Max with Flag in Power Query M

[WSCTech]

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 is loaded. 

ChatGPT has given me this, which results in no syntax errors, but is returning a "Table" object into each row, rather than a value. I think it's almost there - but I am missing something...

let
    Source = YourSource,  // Replace 'YourSource' with your data source
    SortedTable = Table.Sort(Source, {"Index", Order.Ascending}),
    AddMaxA = Table.AddColumn(SortedTable, "AMaxFlag", each 
        let
            currentA = [A],
            currentRow = [Index],
            precedingRows = Table.SelectRows(SortedTable, each [Index] <= currentRow),
            runningMax = List.Max(precedingRows[A])
        in
            if currentBit = runningMax then 1 else 0
    )
in
    AddMaxA

This table shows my desired result. 

Index	DateTime		A	AmaxFlag
0	2023-10-31 05:36:01		100	1
1	2023-10-31 05:36:02		101	1
2	2023-10-31 05:36:03		102	1
3	2023-10-31 05:36:04		101	0
4	2023-10-31 05:36:05		99	0
5	2023-10-31 05:36:06		102	0
6	2023-10-31 05:36:07		103	1
7	2023-10-31 05:36:08		104	1

Thanks in advance for the help!

P.S. M Code is not an option for the language when entering a code snippit??

[jgeddes · Accepted solution]

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]

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!