Forum Discussion
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 Id | Date | Data2 | Data2 |
| A | 3/4/2021 | blah1 | blahA |
| B | 3/4/2021 | blah2 | blahB |
| A | 3/5/2021 | blah3 | blahC |
| B | 3/5/2021 | blah4 | blahD |
| A | 3/6/2021 | blah5 | blahE |
| B | 3/6/2021 | blah6 | blahF |
| C | 3/6/2021 | blah7 | blahG |
| A | 3/7/2021 | blah8 | blah |
| C | 3/7/2021 | blah10 | blah |
What I am trying to do is select only the rows from the latest data into visuals, so all I want is:
| Widget Id | Date | Data2 | Data2 |
| A | 3/7/2021 | blah8 | blah |
| B | 3/6/2021 | blah9 | blah |
| C | 3/7/2021 | blah10 | blah |
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.
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"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.
8 Replies
- AlB
Community Champion
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"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
- Ashish_Mathur
Super User
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
- Ashish_Mathur
My solution still works. See the screenshot.
thanks AlB
My UNION table has ~15mil rows / ~6mil widgets. Is one of these options more efficient than the other?
- AlB
Most likely the PQ option is faster. Give both a try though. They're both simple to implement
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.
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 () )Best Regards
