Forum Discussion

naelske_cronos's avatar
naelske_cronos
Advocate II
4 years ago
Solved

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 ...
  • AlexisOlson's avatar
    4 years ago

    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"