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
RuiPatricio
Frequent Visitor

Select most recent row for each item

I have a Table which is created though a UNION of some queries.

The table has many rows for each widget that I care about -- basically one row for every day.

So it looks somewhat like this:

Widget IdDateData2Data2
A3/4/2021blah1blahA
B3/4/2021blah2blahB
A3/5/2021blah3blahC
B3/5/2021blah4blahD
A3/6/2021blah5blahE
B3/6/2021blah6blahF
C3/6/2021blah7blahG
A3/7/2021blah8blah
C3/7/2021blah10blah

 

What I am trying to do is select only the rows from the latest data into visuals, so all I want is:

Widget IdDateData2Data2
A3/7/2021blah8blah
B3/6/2021blah9blah
C3/7/2021blah10blah

 

ie: I want just the lastest row's data per widget

 

Help?

 

 

*EDIT: Not each widget will have data for every day, so above, WidgetB's data would be form the 6th, whereas A & C are from the 7th.

1 ACCEPTED SOLUTION
AlB
Super User
Super User

Hi @RuiPatricio 

You can do this in DAX (1 below) or in the Query editor (2). See it all at work in the attached file.

 

1. Create a new calculated table

Table2 = 
FILTER (
    Table1,
    Table1[Date]
        = CALCULATE ( MAX ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[Widget Id] ) )
)

2. Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY05DoAwDAT/4jpSDnJAGcLxiCgFVBT8vwaBQRulGks7GudMkQR10kqjjL7P/dyOj5GKyDS2gmGOj/AWHAodM0GhEixzgoJHwTFnKFSCZy6PkFohMFd4EVDomfCh2gfcU7tr9QvlAg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Widget Id" = _t, Date = _t, Data2 = _t, Data2_2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Widget Id", type text}, {"Date", type date}, {"Data2", type text}}),

    #"Grouped Rows" = Table.Group(#"Changed Type", {"Widget Id"}, {{"Count", each Table.Max(_,"Date") }}),
    #"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"Date", "Data2", "Data2_2"}, {"Date", "Data2", "Data2_2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Count",{{"Date", type date}, {"Data2", type text}, {"Data2_2", type text}})
in
    #"Changed Type1"

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

View solution in original post

8 REPLIES 8
v-yiruan-msft
Community Support
Community Support

Hi @RuiPatricio ,

You can create a measure as below to get the latest date:

 

Latest date = 
VAR _maxdate =
    CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Widget Id] ) )
RETURN
    IF ( SELECTEDVALUE ( 'Table'[Date] ) = _maxdate, _maxdate, BLANK () )

 

Latest date.JPGBest Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

thanks @Ashish_Mathur 

I was not clear in my question (& I've edited my post). 

Not every widget will have a row for the latest day; the data I'm looking for is the latest data for that particular widget

My solution still works.  See the screenshot.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
RuiPatricio
Frequent Visitor

thanks @AlB 

My UNION table has ~15mil rows / ~6mil widgets. Is one of these options more efficient than the other?

@RuiPatricio 

Most likely the PQ option is faster. Give both a try though. They're both simple to implement

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

 

AlB
Super User
Super User

Hi @RuiPatricio 

You can do this in DAX (1 below) or in the Query editor (2). See it all at work in the attached file.

 

1. Create a new calculated table

Table2 = 
FILTER (
    Table1,
    Table1[Date]
        = CALCULATE ( MAX ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[Widget Id] ) )
)

2. Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY05DoAwDAT/4jpSDnJAGcLxiCgFVBT8vwaBQRulGks7GudMkQR10kqjjL7P/dyOj5GKyDS2gmGOj/AWHAodM0GhEixzgoJHwTFnKFSCZy6PkFohMFd4EVDomfCh2gfcU7tr9QvlAg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Widget Id" = _t, Date = _t, Data2 = _t, Data2_2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Widget Id", type text}, {"Date", type date}, {"Data2", type text}}),

    #"Grouped Rows" = Table.Group(#"Changed Type", {"Widget Id"}, {{"Count", each Table.Max(_,"Date") }}),
    #"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"Date", "Data2", "Data2_2"}, {"Date", "Data2", "Data2_2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Count",{{"Date", type date}, {"Data2", type text}, {"Data2_2", type text}})
in
    #"Changed Type1"

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

I'm using the PQ approach with Group > Expand -- this is lookig promising. 

Thanks @AlB 

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.