Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.