Latest Record Indicator by Account

[matsahiro]

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                 | $22  |  1/1/2020

2                 | $15  |  1/1/2001

2                 | $17  |   2/4/2005

What would the power query code look like if I want an indicator indicating which record is the latest by account id? Return 1 if the record is the latest record, and return 0 if false. It would look something like this. 

Account ID | Sale  |   Date          |   Latest Record Indicator

1                 | $10  |  1/1/2017    |              0

1                 | $20  |  1/1/2019    |              0

1                 | $22  |  1/1/2020    |              1

2                 | $15  |  1/1/2001    |              0

2                 | $17  |   2/4/2005   |              1

I'd like to create this indicator as a calculated column in power query, not dax if possible. Any thoughts? 

[Vera_33 · Accepted solution]

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"

[matsahiro]

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!

[Vera_33]

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

[matsahiro]

This ended up working great. Very impressed. Thank you!