Understanding Concat and Distinct together

[AaronO]

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)

[WarrenBelz]

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

[AaronO]

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

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?

[AaronO]

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.

[AaronO]

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.

[WarrenBelz]

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.

[AaronO]

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.