Forum Discussion

Jigar1276's avatar
Jigar1276
Icon for Helper I rankHelper I
5 years ago
Solved

Add custom column by finding the latest date for each "key" column

Hi,

 

I want to add one column to following table where I can get flag about latest "Status Change Date" for each "key".

For bellow screenshot, I want "Yes" for key "st-273" for blue highlated date row. Simillary for key "st-272" lastest date is yellow highlated row.

Thanks in advance for any help in this regards.

Need Help
  • Jakinta's avatar
    Jakinta
    5 years ago

    Hope this can help.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc4xCoAwEETRq8jWgcmsCXHt9Boh97+GhlTiwFSvGH7vdlmywA7nls88ZyMtbuChnHDpFe7KCxjKXfi9/Pc/nURR/sY35YH64fEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [key = _t, #"Status Change Date" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Status Change Date", type datetime}}),
    GroupedRows = Table.Group(ChangedType, {"key"}, {{"MaxDate", each List.Max([Status Change Date]), type nullable datetime}}),
    #"Added Custom" = Table.AddColumn(ChangedType, "Custom", each if let k=[key] in [Status Change Date] = Table.SelectRows(GroupedRows, each ([key]=k )){0}[MaxDate] then "Yes" else "")
in
    #"Added Custom"

2 Replies

  • Jakinta's avatar
    Jakinta
    Icon for Solution Sage rankSolution Sage
    5 years ago

    Hope this can help.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc4xCoAwEETRq8jWgcmsCXHt9Boh97+GhlTiwFSvGH7vdlmywA7nls88ZyMtbuChnHDpFe7KCxjKXfi9/Pc/nURR/sY35YH64fEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [key = _t, #"Status Change Date" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Status Change Date", type datetime}}),
    GroupedRows = Table.Group(ChangedType, {"key"}, {{"MaxDate", each List.Max([Status Change Date]), type nullable datetime}}),
    #"Added Custom" = Table.AddColumn(ChangedType, "Custom", each if let k=[key] in [Status Change Date] = Table.SelectRows(GroupedRows, each ([key]=k )){0}[MaxDate] then "Yes" else "")
in
    #"Added Custom"