cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
naelske_cronos
Helper I
Helper I

While Do Loop in Power Query M

Hello,

 

I want to add a new column with as results "True" or "False" using the following data showing in the images.

Each row has a datetime value which is the datetime of the file that has been imported and a state which is CANCELLED or ACTIVATED.

  • If the row with the most recent datetime has a state equal to ACTIVATED, then the new column value for that row should be equal to "True" and the rest "False".
  • If the row with the most recent datetime has a state equal to CANCELLED, then the new column value for that row should be equal to "False" and the next row with the second most recent datetime where the state is equal to ACTIVATED should be equal to "True" and the rest "False".

Example1.PNG

  • But if the two first rows with the most recent dates are both equal to "CANCELLED", then the third most recent datetime where the state is equal to ACTIVATED should be equal to "True" and the rest "False" and so on...

Example2.PNG

 

My guesses are using some sort of a list.generate() or recursive function as it looks like a kind of while do function but maybe there are other solutions.

 

An idea on how to get my result?

 

Thank you!

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

It looks like you want to tag the first ACTIVATED row. To do this, I'd recommend grouping by [State], taking the min over the date column, filtering [State] = "ACTIVATED", and then merging this back with the step before grouping. Then you can transform the column based on whether or not it's empty.

 

Here's a sample query you can paste into the Advanced Editor and walk through the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcnb0c3b18XF1UdJRMtQ31DcyMLQEMZVidVAljfSB0MDIAMQESzo6h3iGOYaAJY31jUGShiAmhqSJvglI0gjEVIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [State = _t, Date = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"State", type text}, {"Date", type date}, {"Index", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"State"}, {{"MinDate", each List.Min([Date]), type nullable date}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([State] = "ACTIVATED")),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"State", "Date"}, #"Filtered Rows", {"State", "MinDate"}, "New Column", JoinKind.LeftOuter),
    #"Transformed Column" = Table.TransformColumns(#"Merged Queries", {{"New Column", each not Table.IsEmpty(_), type logical}})
in
    #"Transformed Column"

View solution in original post

3 REPLIES 3
AlexisOlson
Super User
Super User

It looks like you want to tag the first ACTIVATED row. To do this, I'd recommend grouping by [State], taking the min over the date column, filtering [State] = "ACTIVATED", and then merging this back with the step before grouping. Then you can transform the column based on whether or not it's empty.

 

Here's a sample query you can paste into the Advanced Editor and walk through the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcnb0c3b18XF1UdJRMtQ31DcyMLQEMZVidVAljfSB0MDIAMQESzo6h3iGOYaAJY31jUGShiAmhqSJvglI0gjEVIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [State = _t, Date = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"State", type text}, {"Date", type date}, {"Index", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"State"}, {{"MinDate", each List.Min([Date]), type nullable date}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([State] = "ACTIVATED")),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"State", "Date"}, #"Filtered Rows", {"State", "MinDate"}, "New Column", JoinKind.LeftOuter),
    #"Transformed Column" = Table.TransformColumns(#"Merged Queries", {{"New Column", each not Table.IsEmpty(_), type logical}})
in
    #"Transformed Column"

@AlexisOlson 

 

Thank you very much! This is what I want.

Looks a lot easier than using a List.Generate() and I don't even know if I can get the same result with a List.Generate().

 

 

Best regards

You could do this with List.Accumulate or List.Generate but you don't have to. 🙂

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors