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

 

IndexDateTime AAmaxFlag

0

2023-10-31 05:36:01 1001
12023-10-31 05:36:02 1011
22023-10-31 05:36:03 1021
32023-10-31 05:36:04 1010
42023-10-31 05:36:05 990
52023-10-31 05:36:06 1020
62023-10-31 05:36:07 1031
72023-10-31 05:36:08 1041

 

 

Thanks in advance for the help!

 

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

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.

2 Replies

  • 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!