Forum Discussion
Solved
Latest Record Indicator by Account
Hello, I have a table of table that looks something like this. Account ID | Sale | Date 1 | $10 | 1/1/2017 1 | $20 | 1/1/2019 1 | $2...
Hi matsahiro
You can GROUPBY to find the max date then join back to add the indicator, paste in Advanced Editor.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUVJQMTQ4tEAByDIw1AciIwNDc6VYHZikEbqkJbKkEaqkkQFY0ghirCmqpIEhsqQ5VNJI38AEJGmqFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account ID " = _t, #"Sale " = _t, Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account ID ", Int64.Type}, {"Sale ", type text}, {"Date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Account ID "}, {{"MaxDate", each List.Max([Date]), type nullable date}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Account ID "}, #"Grouped Rows", {"Account ID "}, "Grouped Rows", JoinKind.LeftOuter), #"Added Custom" = Table.AddColumn(#"Merged Queries", "Indicator", each if [Date] = [Grouped Rows][MaxDate]{0} then 1 else 0) in #"Added Custom"
Hello Vera, thank you for the help! That's definitely something I thought about, but would rather not create extra queries if possible. Are there any other workarounds you're familiar with? Thanks!
Hi matsahiro
It is not a new query, the same query...I was showing you how to do it so you can see, just GROUPBY and join back then delete the the column, stay with the original query