cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AaronO
Helper II
Helper II

Understanding Concat and Distinct together

Can anyone explain to me why this works to produce a list of strings as a delimited string:

 

Match(Concat(Filter('Plant Risks','Risk Card'.Id = varCurrentRisk.ID), 'Plant:Title'.Value & ", "),"^(?<trim>.*), $" ).trim

 

While this does not?:

 

With({_A: Filter('Plant Risks','Risk Card'.Id = varCurrentRisk.ID)},
Match(Concat(Distinct(_A,'Plant:Title'.Value),Result,", "),"^(?<trim>.*), $" ).trim)

7 REPLIES 7
WarrenBelz
Super User
Super User

Hi @AaronO ,

I am not sure this will even work (I cannot test it presently), but your issue will be what you are trying to use the Distinct Function on (Lookup column)

With(
   {
      _A: 
      AddColumns(
         Filter(
            'Plant Risks',
            'Risk Card'.Id = varCurrentRisk.ID
         ),
         "PlantTitle",
         'Plant:Title'.Value
      )
   },
   Match(
      Concat(
         Distinct(
            _A,
            PlantTitle
         ),
         Result,
         ", "
      ),
      "^(?<trim>.*), $" 
   ).trim
)

 

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.

Visit my blog Practical Power Apps

@WarrenBelz thanks for your reply.  That was a good thought, but this still gives the same result.  No error, but also no string output.

w1sd0m
Helper III
Helper III

i think this has something with trying to enumerate on a distinct function

can you put the distinct filter in the with statement so the match is only enumerating the filtered list?

 

Match(
    Concat(
        Distinct(
            AddColumns(
                Filter(
                    'Plant Risks',
                    'Risk Card'.Id = varCurrentRisk.ID
                ),
                "PlantTitle",
                'Plant:Title'.Value
            ),
            PlantTitle
        ),
        Result,
        ", "
    ),
    "^(?<trim>.*), $"
).trim

 

Good thought, but same result.

 

Edit:  I realized I misunderstood your suggestion.  Let me try again.

With(
   {
      _A: 
         Distinct(
             Filter(
            'Plant Risks',
            'Risk Card'.Id = varCurrentRisk.ID
         ),'Plant:Title'.Value)
   },
   Match(
      Concat(
          _A,
            Result,
         ", "
      ),
      "^(?<trim>.*), $" 
   ).trim
)

And:

With(
   {
      _A: 
         Distinct(
             AddColumns(
             Filter(
            'Plant Risks',
            'Risk Card'.Id = varCurrentRisk.ID
         ),"PlantTitle",'Plant:Title'.Value),PlantTitle)
   },
   Match(
      Concat(
          _A,
            Result,
         ", "
      ),
      "^(?<trim>.*), $" 
   ).trim
)

 

Both don't work.  Same result.

Hi @AaronO ,

I will leave you with one other suggestion - consider doing the Lookup in Power Apps - I have not used SharePoint Lookup columns in years as they generally serve no purpose in Power Apps other than to give you the grief you are currently experiencing on many otherwise simple functions. This is particularly relevant on "secondary" columns like yours.

They are a historical column type designed to work with the SharePoint and InfoPath interfaces. I have a blog on this general subject if you are interested.

Thanks for that advice.  I was resistant to the lookup fields myself but I have other developers also contributing and it wasn't something I pushed hard on, not knowing what problems it would cause.  I will visit your blog and see what you have.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (2,107)