Forum Discussion
Jigar1276
Helper I
Helper IAdd 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 dat...
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"
Jakinta
Solution Sage
Solution SageHope 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"