Return Value based on MAX Date Created

[mouzzampk]

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

[lbendlin]

Not clear what you are trying to achieve.  Are you looking for the max date for each invoice number?

[mouzzampk]

Is it possible to put this in a separate column but also keep the Date Created Column as in my example above? 

[lbendlin]

Sure, but why?

[mouzzampk]

I am trying to highlight which one is MAX and MIN i.e., Old Invoice or New Invoice.

[dufoq3 · Accepted 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

[mouzzampk]

Thank you so much

[dufoq3]

You're welcome.

[mouzzampk]

That's correct.