Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Resident Rockstar
Resident Rockstar

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
Resident Rockstar
Resident Rockstar

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors