Trying to combine certain records in a dataset

(0) Share

Report

Posted on by bobgodin

380

I have a Powerapp and a Gallery presenting meter readings from a Sharepoint List

I am trying to group on a column called "auth" and then sum the difference between the min and max reading to get water usage for a period in time (between 2 datepickers)

Consider the attached screenshot:
In the 4th and 5th record we have the following values in the auth column:

621563

621563;621564

both auths are owned by the same Client so I want to add the usage for both records but only show the combined auths value which in this case is 621563;621564

so the logic is: If the auth value contains 2 or more auths AND 1 of those auths exists as a single auth on a seperate record then combine and only show the multiple auths value

so if we had:

123456

123457;123459l;123456

876543;872514;887665

887665

I want to display only this:

123457;123459l;123456

876543;872514;887665

hope I have explained myself well enough 🙂

thanks

Categories:

Building Power Apps

All responses (6)

Answers (1)

Sort by

VishalJhaveri 1,169 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
ClearCollect( CombinedAuths, Filter( YourSharePointList, CountRows( Filter( YourSharePointList, CountRows( Filter( Split( ThisItem.Auth, // Split the auth value into an array of auths ";" ), AuthColumn = Result ) ) = 1 // Check if any of the auths exist as a single auth on another record ) ) > 0 // Check if there are multiple auths and at least one matches a single auth elsewhere ) ); // Display the combined auth values in your Gallery Items = CombinedAuths
Please try this if it works.
bobgodin 380 on at

Like
a
(0)

Report
Copy link

Link copied!

@VishalJhaveri thanks a lot for this....just a bit unsure where to put the ClearCollect statement...I tried in the OnVisible property of the screen but it didn't like it - "CountRows is not supported" and "ThisItem isn't recognised see screenshot - thanks again
WarrenBelz 156,748 Most Valuable Professional on at

Like
a
(2)

Report
Copy link

Link copied!
Hi @bobgodin ,

This structure is working on my end with a collection the same as the data you posted

With( { _Records: AddColumns( SPList, _Auths, Split( Auth, ";" ).Value ) }, With( { _OneItem: Filter( _Records, CountRows(_Auths) = 1 ), _MoreItems: Filter( _Records, CountRows(_Auths) > 1 ) }, Ungroup( Table( {Value: _MoreItems}, { Value: Filter( _OneItem, !( Auth in Ungroup( _MoreItems, _Auths ).Value ) ) } ), Value ) ) )

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
Verified answer

bobgodin 380 on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz thanks heaps Warren - I think it is doing the job perfectly - had to add a comma and changed Auth to auth but otherwise spot on - will check deeper to make sure....
WarrenBelz 156,748 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks @bobgodin ,

Not sure where the missing comma is - I copied the code straight from the working version in my test app - I will fix if necessary if you point it out.
bobgodin 380 on at

Like
a
(0)

Report
Copy link

Link copied!

hi @WarrenBelz the comma I added was just after MeterReadings (mySPList):
AddColumns(
MeterReadings,
_Auths,