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:

Connecting to Data

usage.png

I have the same question (0)

All responses (6)

Answers (1)

VishalJhaveri 1,167 Moderator on at

Like (0)

Report
Re: Trying to combine certain records in a dataset

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.

Was this reply helpful? Yes No
bobgodin 380 on at

Like (0)

Report

Re: Trying to combine certain records in a dataset

@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

clearcollect.png

Was this reply helpful? Yes No
WarrenBelz 151,881 Most Valuable Professional on at

Like (2)

Report
Re: Trying to combine certain records in a dataset

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

Was this reply helpful? Yes No
Verified answer

bobgodin 380 on at

Like (0)

Report

Re: Trying to combine certain records in a dataset

@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....

Was this reply helpful? Yes No
WarrenBelz 151,881 Most Valuable Professional on at

Like (0)

Report

Re: Trying to combine certain records in a dataset

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.

Was this reply helpful? Yes No
bobgodin 380 on at

Like (0)

Report

Re: Trying to combine certain records in a dataset

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

Was this reply helpful? Yes No