Answered

Formula to group sharepoint list items that meet criteria, then identify the highest value in a column and move to a new column.

(0) Share

Report

Posted on by JScherff

Hello friends!

I've been trying to figure out how to best accomplish a task and have looked at both PowerApps formulas as well as PowerAutomate Flows with no luck.

I have a sharepoint list that contains completed projects with information like below:

COLUMNS TO UPDATE

ID ProdLine Items Affected Description Monthly Proj. Impact Annual Savings Adj. Mnth Imp Adj. Ann Sav

1 PMP D123, F334 Update to pumps $5,334 $112,343 $5,334 $0

2 PMP D123, F334 Upgrade power $4,232 $115,232 $0 $115,232

3 PMP D123, R553 Upgrade power $4,664 $28,223 $4,664 $28,223

4 PMP F112, G743 Update to pumps $6,232 $121,112 $6,232 $121,112

5 PMP D123, F334 Upgrade power $3,232 $111,232 $0 $0

What I need to do is update the end columns (currently blank) with the HIGHER of each number when the Items Affected are the exact same.

What this does for me is allow me to show financial impacts without "double-dipping" on projects that have the same exact Item numbers. The best way for me to show the impact, per management, is to just show the larger of the two numbers for a project. These two end columns will then sum and be displayed on a dashboard.

I'm having difficulty in combining all these steps:

looks at each line (record) in the list and compares to entire list
Matches all rows / records that have the same model numbers affected
Pull out the highest value for each of the two columns (Monthly Proj. Impact and Annual Savings) if there is a match and updates the others to $0
Just copies over the amounts if they are records that have no other matching records with the same Items Affected.

Appreciate any assistance with this!

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (2)

Sort by

Verified answer

mmollet 3,187 on at

Like
a
(2)

Report
Copy link

Link copied!

This seems pretty involved but off the top of my head here are some ideas to get started with.

1- if you use distinct to get all the unique items using 'items collected' column then you can loop through that list and do whatever data manipulation you need to get started.
2- you can then use this: Filter(GroupBy(someVar,"Name","Other"), CountRows(Other)>1) in order to get all the duplicates. You can then change what you need to 0s in this list as you know they are all duplicates.
3- another though is sorting them by 'items affected' in the flow will make the flow option alot easier. You can have a variable set to the first item of the list then use an apply to each that loops through that list. each time the loop iterates check if the 'items affected' has changed. If it is the same then its a duplicate and you can change what you need but if it has changed then get the max's and set them and then also dont forget to set the variable to the new 'items affected'. This process can repeat until done.
4- You could always try to split this up into 2 process if that helps. Do a quick run through that just gets the max and sets them all and then use the formula in I gave in step 2 above with a forall to zero out all the duplicates.

Hope this at least helps you getting started!

Was this reply helpful? Yes No
Verified answer

WarrenBelz 156,434 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @JScherff ,

@mmollet is on the right track - you need this

With( { wMax: AddColumns( GroupBy( SPListName, "Items Affected", "Data" ), "MaxImpact", Max( Data, 'Monthly Proj. Impact', ), "MaxSavings", Max( Data, 'Annual Savings' ) ) }, ForAll( wMax As aMax, UpdateIf( SPListName, 'Items Affected' = aMax.'Items Affected', { 'Adj. Mnth Imp': aMax.MaxImpact, 'Adj. Ann Sav': aMax.MaxSavings } ) ) )

Note this is not Delegable on several levels (nor can you make it so), so you would have to filter large data sets into smaller "chunks" if your list numbers are over your Delegation limit.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
JScherff 72 on at

Like
a
(1)

Report
Copy link

Link copied!

Thank you both for the great information and direction. I eventually went with @WarrenBelz's solution even thought you both were basically providing the same direction. I appreciate the support on this!

Was this reply helpful? Yes No
mmollet 3,187 on at

Like
a
(1)

Report
Copy link

Link copied!

If between me or Warren's I would always choose the one you did xD lol hes the pro im just a guy that likes power apps 😉 The man knows ALOT about Power Apps. Glad we could help!

Was this reply helpful? Yes No