Select Max ID for each Category

(0) Share

Report

Posted on by ragnezza

Hello - I have a simple Dataverse table that looks like this:

Category	ID
AAA	1000
AAA	1200
AAA	2200
BBB	3000
BBB	2800
CCC	1500
CCC	4000

How can I output the MAX ID for each Category (AAA = 2200, BBB = 3000, CCC = 4000)?

As shown in the attached screenshot, my flow performs the following actions:
1. Lists the rows form the Dataverse table

2. Selects the Category field and removes duplicates

3. Selects the (unique) IDs

4. Pulls the Category Name for each (unique) Category

From here, I thought I could simply use max('ID') but it returns the max('ID') of the whole table even if I'm using the 'Apply to each' action.

Thanks!

Flow Unique IDs.png

Your file is currently under scan for potential threats. Please wait while we review it for any viruses or malicious content.

Categories:

Building flows

Power Automate Mobile App

Process mining

I have the same question (0)

All responses (4)

Answers (0)

Sort by

Chriddle 8,708 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
But if you are not afraid of a little xPath, you can also try this:

From:

union( xpath( xml( addProperty( json('{}'), 'Root', addProperty( json('{}'), 'Item', outputs('Compose') ) ) ), '//Item/Category/text()' ), json('[]') )

Map Category:

item()

Map MaxId:

int( first( xpath( xml( addProperty( json('{}'), 'Root', addProperty( json('{}'), 'Item', outputs('Compose') ) ) ), concat('//Item[Category = "', item(), '" and not(ID < preceding-sibling::Item[Category = "', item(), '"]/ID) and not(ID < following-sibling::Item[Category = "', item(), '"]/ID)]/ID/text()') ) ) )

Was this reply helpful? Yes No
Chriddle 8,708 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

If you want to stick to your approach, you need to filter the ids per Category, sort and reverse them, then take each first item.

Was this reply helpful? Yes No
Chriddle 8,708 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
A more traditional way without loops:

The key is the following expression that first sorts the array by ID, then by Category (and reverses it):

reverse( sort( sort( outputs('Compose'), 'ID' ), 'Category' ) )

The Select function checks whether each element in such a sorted array has the same category as the previous element, and if so, sets it to null.

The Filter function simply removes the empty elements.

Select

From:

range(0, length(outputs('Compose')))

Map:

if( equals(item(), 0), reverse( sort( sort( outputs('Compose'), 'ID' ), 'Category' ) )[0], if( equals( reverse( sort( sort( outputs('Compose'), 'ID' ), 'Category' ) )[item()]['Category'], reverse( sort( sort( outputs('Compose'), 'ID' ), 'Category' ) )[sub(item(), 1)]['Category'] ), null, reverse( sort( sort( outputs('Compose'), 'ID' ), 'Category' ) )[item()] ) )

Filter array

From:

reverse(body('Select'))

Filter:

item() is not equal to null

Was this reply helpful? Yes No
Suggested answer

Hayat135 6 on at

Like
a
(0)

Report
Copy link

Link copied!

If you have a master table for Category, you can first retrieve the list of categories from the master table by using the List Rows action. To get unique category names, you can apply a Union expression to the retrieved data to generate distinct category values.

After obtaining the distinct category names, add an Apply to Each action and pass the distinct category list into the loop. Inside this loop, add another List Rows action and use a Filter Query where the logical column name of Category is equal to the current item from the Apply to Each loop. This allows you to retrieve records belonging to each category separately.

Next, use a Select action with the output of the filtered List Rows result and map the ID field. After that, add a Compose action and use the expression:

max(outputs('Select'))

This will return the maximum ID value for each category within the loop.

Was this reply helpful? Yes No