cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jug
Frequent Visitor

Select rows with the most recent date without groupby

Hi, I would like to know if someone could help me with the following question:

 

I need to filter only the columns with the most recent date (and smaller than today, if possible), filtering the maximum for each ID and keeping the information from the rest of the table for that row. I tried with group by, but it's a lot of data and it doesn't load, I tried to filter and delete duplicates, but I'm using directquery so it's not possible to delete data.

 

When using the filter when modifying the table, it returns this formula:


= Table.SelectRows(TABLE1, let latest = List.Max(TABLE1[Date]) in each [Date] = latest)

would it not be possible to edit it  and mention that I would like it to return all rows, selecting only the most recent/maximum of each ID?

 

Example

Jug_0-1674255906270.png

 

Should result in this:

 

Jug_1-1674255931886.png

 

Thanks!

 

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}, {"Address", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] < DateTime.Date(DateTime.LocalNow())),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"ID"}, {{"Count", each Table.Max(_,"Date")}}),
    #"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"Date", "Address"}, {"Date", "Address"})
in
    #"Expanded Count"

Hope this helps.

Untitled.png


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

I replaced all the field names with the real ones in the table correctly, the problem is that after the "IN" if I put only  #"Expanded Count" it does not work, I believe it needs to mention the table too

After in, you always have to specify the name of the last step.


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

Unfortunately, it does not work because it has a group function and this is not supported with DirectQuery.

I was able to use this filter of the most recent date only in the dashboard, but I need to have information from different data tables in the same table.
For example, showing the most recent address from the address table and the customer name from the client table, they have already been related by the client code, but I need the filter so that the table does not bring all the registered addresses of the same client.

I would need to bring information from two tables and your formula includes only one. Would it be possible to include fields from another table?

Unfortunately I could not use these ideas, because I'm trying to do this filter in the modification of the query, so then I can create a relationship with another table, so create a measure does not help me in this, from what I saw, it does not appear as a column in the table. Thus, I can't use.

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors