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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
karimk
Helper III
Helper III

M Formula - Retrieve the max number for a repeating row

Hello,

I have a database with multiple rows where each one has a person and some of their info, including an ID.

People can show up in more than one row, and their ID can be different. 

 

I want to be able to show in a new column the max ID number for each person. All of this in Power Query (M).

Can someone help? I have provided a simple database below.

Onedrive link 

 

Thanks,

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

You can select the rows with the same name and then get the max ID like this:

List.Max(Table.SelectRows(#"Changed Type", 
(r) => r[name] = [name] )[ID])

#"Changed Type" is the previous step

View solution in original post

3 REPLIES 3
HotChilli
Super User
Super User

Will depend on the size of the data I suppose.  You could check to see if query folding is taking place.

Also you could also investigate the buffer functions to see if PQ can read files once instead of multiple times.

HotChilli
Super User
Super User

You can select the rows with the same name and then get the max ID like this:

List.Max(Table.SelectRows(#"Changed Type", 
(r) => r[name] = [name] )[ID])

#"Changed Type" is the previous step

Hey @HotChilli ,

Your suggestion worked, but now the desktop file takes 1 hour to update. Any ideas why?

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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