cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
matsahiro
Helper II
Helper II

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                 | $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? 

 

1 ACCEPTED SOLUTION
Vera_33
Super User
Super User

Hi @matsahiro 

 

You can GROUPBY to find the max date then join back to add the indicator, paste in Advanced Editor.

Vera_33_0-1616635708592.png

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"

 

View solution in original post

4 REPLIES 4
Vera_33
Super User
Super User

Hi @matsahiro 

 

You can GROUPBY to find the max date then join back to add the indicator, paste in Advanced Editor.

Vera_33_0-1616635708592.png

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

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors