Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
I have attached spreadsheet. Please let me know if I need to provide more information.
Thank you
Solved! Go to Solution.
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
Not clear what you are trying to achieve. Are you looking for the max date for each invoice number?
Is it possible to put this in a separate column but also keep the Date Created Column as in my example above?
Sure, but why?
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
Thank you so much
That's correct.