Forum Discussion
Solved
Return Value based on MAX Date Created
Hi, need some help with MAX function. I am looking for a way to return the Invoice based on MAX date created e.g., Latest Date column. https://1drv.ms/x/s!Ah6KmIniO5ZMgbVF5NYJDWSg5secOg?e=FWebfN ...
Result:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdHBDcMwCAXQXXyOBHwbMCP00gWi7L9GHTmtUiqr56cvPrDv5fG0qE1UUbZSCUpgcDm2RMIk7TT5mHGTsGFBaN+xG/UkYJM2xElsQUGSUzWsxxylC3JCJDKFntRJ6oLGXnlW71wvQq54NyQLFeisv5RcI5S9zwyv6UcA/yPpIc7u7Sr3PuDxAg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Invoice = _t, #"Date Created" = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"Date Created", type date}}, "en-US"), // Added [Old New] column inside each [All] table GroupedRows = Table.Group(ChangedType, {"Invoice"}, {{"All", each Table.AddColumn(_, "Old New", (x)=> if x[Date Created] = List.Max([Date Created]) then "New" else "Old", type text), type table}}), CombinedNewOld = Table.Combine(GroupedRows[All]) in CombinedNewOld
I am trying to highlight which one is MAX and MIN i.e., Old Invoice or New Invoice.
Result:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdHBDcMwCAXQXXyOBHwbMCP00gWi7L9GHTmtUiqr56cvPrDv5fG0qE1UUbZSCUpgcDm2RMIk7TT5mHGTsGFBaN+xG/UkYJM2xElsQUGSUzWsxxylC3JCJDKFntRJ6oLGXnlW71wvQq54NyQLFeisv5RcI5S9zwyv6UcA/yPpIc7u7Sr3PuDxAg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Invoice = _t, #"Date Created" = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Date Created", type date}}, "en-US"),
// Added [Old New] column inside each [All] table
GroupedRows = Table.Group(ChangedType, {"Invoice"}, {{"All", each Table.AddColumn(_, "Old New", (x)=> if x[Date Created] = List.Max([Date Created]) then "New" else "Old", type text), type table}}),
CombinedNewOld = Table.Combine(GroupedRows[All])
in
CombinedNewOld