Skip to main content

Notifications

Power Apps - Building Power Apps
Answered

Trying to use Concat to merge values in a string with constraint

Posted on by 359
I have a Sharepoint list called MeterReadings which has a field called auth. This field can contain one value like "9876AP6829" or many values like "9876AP6829; 9875AP6829; 9874AP6829"

I am trying to merge all the values together IF 1 value is present more than once within that subset - please see the example attached for what I am trying to achieve
 
My Gallery Items is:
GroupBy(
    SortByColumns(
        Filter(
            MeterReadings,
            readdate >= (DatePickerFrom_5.SelectedDate) && readdate <= DatePickerTo_5.SelectedDate && (StartsWith(
                auth,
                TextInput2.Text
            ) || StartsWith(
                clientname,
                TextInput2.Text
            ))
        ),
        "auth"
    ),
    auth,
    Data
)
A little more explanation could be useful; The MeterReadings table contains water meter readings for a client/s. A client is identified by an auth or a number of auths or a combination of auths separated by "; "
If, for example there is a MeterReading against auth 05629K AND there is another reading against 05629K; 624367; 624368; 624918 it's very likely that this is the same client and so we want to combine or merge all the auths that look like they are related and then calculate water usage based on ALL those meter readings. I hope I have explained this correctly - the spreadsheet attached has sample data and expected outcomes
 
I would like to put the Concat within/on the end of this if possible
thanking you :)
Categories:
  • Verified answer
    WarrenBelz Profile Picture
    WarrenBelz 143,591 on at
    Trying to use Concat to merge values in a string with constraint
    You can get the filter in near the top as shown below, but the auth column no longer exists (you wanted mergedauths) as per the sample you posted and it is already grouped to achieve this added column. 
    ShowColumns(
       AddColumns(
          AddColumns(
             Filter(
                colMRsbyWMA,
                readdate >= DatePickerFrom_3.SelectedDate && 
                readdate <= DatePickerTo_3.SelectedDate && 
                StartsWith(
                   wma,
                   Dropdown2_1.Selected.Result
                ) && 
                (
                   TextInput2.Text in auth || 
                   StartsWith(
                      clientname,
                      TextInput2.Text
                   )
                )
             ),
             Base,
             If(
                ";" in auth,
                First(
                   Split(
                      auth,
                      ";"
                   )
                ).Value,
                auth
             )
          ),
          mergedauths,
          LookUp(
             AddColumns(
                GroupBy(
                   Ungroup(
                      AddColumns(
                         AddColumns(
                            colMRsbyWMA,
                            Auths,
                            Split(
                               auth,
                               ";"
                            ).Value
                         ),
                         MainAuth,
                         First(Auths).Value
                      ),
                      Auths
                   ),
                   MainAuth,
                   Data
                ),
                AllAuths,
                Concat(
                   Distinct(
                      Data,
                      Value
                   ),
                   Value,
                   ";"
                )
             ),
             MainAuth = Base
          ).AllAuths
       ),
       mergedauths,
       readdate,
       reading
    )
     
    Please click Does this answer your question 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 a Like.
    MVP (Business Applications)    Visit my blog Practical Power Apps    Buy me a coffee
  • bobgodin Profile Picture
    bobgodin 359 on at
    Trying to use Concat to merge values in a string with constraint
    Hi @WarrenBelz thank you so much - that code has done the trick - just one more silly question:
    How can I incorporate that into my Items property for my Gallery:
     
    GroupBy(
        SortByColumns(
            Filter(
                colMRsbyWMA,
                readdate >= DatePickerFrom_3.SelectedDate && readdate <= DatePickerTo_3.SelectedDate && StartsWith(
                    wma,
                    Dropdown2_1.Selected.Result
                ) && (TextInput2.Text in auth || StartsWith(
                    clientname,
                    TextInput2.Text
                ))
            ),
            "auth"
        ),
        auth,
        Data
    )
     
    and btw I am using a collection to start with and then creating a smaller collection when the user chooses a wma value from Dropdown and THAT'S where the Concat/Split etc is applied
  • Verified answer
    WarrenBelz Profile Picture
    WarrenBelz 143,591 on at
    Trying to use Concat to merge values in a string with constraint
    I see it now. I think you will appreciate the complexity of this from the code below - fried a few brain cells on the way . . .
    I have no idea however how your device is going to handle this through an 8,000 records collection (and none of it is remotely Delegable)
    ShowColumns(
       AddColumns(
          AddColumns(
             Table1,
             Base,
             If(
                ";" in auth,
                First(
                   Split(
                      auth,
                      ";"
                   )
                ).Value,
                auth
             )
          ),
          mergedauths,
          LookUp(
             AddColumns(
                GroupBy(
                   Ungroup(
                      AddColumns(
                         AddColumns(
                            Table1,
                            Auths,
                            Split(
                               auth,
                               ";"
                            ).Value
                         ),
                         MainAuth,
                         First(Auths).Value
                      ),
                      Auths
                   ),
                   MainAuth,
                   Data
                ),
                AllAuths,
                Concat(
                   Distinct(
                      Data,
                      Value
                   ),
                   Value,
                   ";"
                )
             ),
             MainAuth = Base
          ).AllAuths
       ),
       mergedauths,
       readdate,
       reading
    )
     
    Please click Does this answer your question 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 a Like.
    MVP (Business Applications)    Visit my blog Practical Power Apps    Buy me a coffee
  • WarrenBelz Profile Picture
    WarrenBelz 143,591 on at
    Trying to use Concat to merge values in a string with constraint
    Hi @bobgodin​​​​​​​
    I am rather confused as to how the right table is extracted from the left one (the logic behind this - where are the additional values being identified from ?)
  • bobgodin Profile Picture
    bobgodin 359 on at
    Trying to use Concat to merge values in a string with constraint
    Hi @WarrenBelz or @RandyHayes is there any chance one of you could help me with this one - I have had no response with a solution so far?
     
    thank you
  • bobgodin Profile Picture
    bobgodin 359 on at
    Trying to use Concat to merge values in a string with constraint
    @ronaldwalcott 
    I just want to modify the list so I can calculate usage based on the new auths value
    I'm accessing 8000 records but I have created a collection on startup which gathers all records
  • ronaldwalcott Profile Picture
    ronaldwalcott 1,167 on at
    Trying to use Concat to merge values in a string with constraint
    Are you just trying to display the items in that manner or modify the list?
    How many records would you be accessing as there would be delegable issues?  

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November 2024 Newsletter…

November 2024 Community Newsletter…

Community Update Oct 28…

Power Platform Community Update…

Tuesday Tip #7 Community Profile Tips…

Welcome to a brand new series, Tuesday Tips…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 143,591

#2
RandyHayes Profile Picture

RandyHayes 76,308

#3
Pstork1 Profile Picture

Pstork1 64,090

Leaderboard