cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MaxDonen
Level: Powered On

Check if a string is unique within a column of a collection

I am sure there's a way to do this! It would be very useful for the project I'm working on.

 

Also, how could you check if a string length is the longest within a collection?

 

I presume the second query is along the lines of len(collectionName,columnName) but not sure of the details after an exhaustive search on PowerApps Support.

 

Thanks.

3 REPLIES 3
chrisog
Level 8

Re: Check if a string is unique within a column of a collection

Hi @MaxDonen,

 

I would assume that you would need to utilise the ForAll function to do this. Are you looking to find whether the string you are searching appears within the column? If so, you can use something like:

 

ForAll(MyDataSource, If(myString in ColumnName, Collect(countMatches, {match:1}))) 

And then you can just use CountRows(countMatches) to determine how many matches there were.

 

Your second question may use a similar idea:

 

ForAll(MyDataSource,Collect(countLen, {value:Len(ColumnName),col:ColumnName2}))

 

This means you can then find the highest value length with something like:

Max(countLen, value)

Or the corresponding value in 'col' with

First(SortByColumns(countLen,"value",Descending)).col

Microsoft v-siky-msft
Microsoft

Re: Check if a string is unique within a column of a collection

Hi @MaxDonen ,

 

Please try with this formulas. If the countrows is 1, it means the string is unique; if the countrows is greater than 1, it means there are many of the same strings in that column.

 

CountRows(Search(CollectionName,"TheString","ColumnName"))

To your second query, the Len function is for string or single column table. the syntax shoud be Len( String ) or Len( SingleColumnTable ).

So try with the following formula to get the maximum of column

 

Max(Len(CollectionName.ColumnName),Result)

And then use the following formula to judge if a string length is the longest. 

Len("TheString") > Max(Len(CollectionName.ColumnName),Result)

Hope this can help.

Best regards,

Sik

 

 

 

 

MaxDonen
Level: Powered On

Re: Check if a string is unique within a column of a collection

@v-siky-msft Hi, thanks for the help. Perhaps I should explain a bit more about my project. Here's a typical dataset:

 

4,1

4,2,2

1

4,2

1,1

2

 

 

Within this set of data, 1 would be considered unique. Even though it's contained elsewhere, the standalone '1' is a unique identifier as long as it appears by itself.  (so 1 is different to 1,1 and 4,1, for example)

Likewise, 4,2 would be a unique identifier as long as it appears by itself.

 

What I am trying to do is use the above letter format to indicate parent/child relationships. In the set of numbers above, 4.2.2 is an ultimate child, as there is nothing below it. But 4.2 is not. Likewise, 1 is not an ultimate child, but 1.1 is. Also, 2 is an ultimate child.

 

<quote>

 

Please try with this formulas. If the countrows is 1, it means the string is unique; if the countrows is greater than 1, it means there are many of the same strings in that column.

 

CountRows(Search(CollectionName,"TheString","ColumnName"))

So unfortunately this does not work for the above example, because it does not consider 2 unique, as this number is found elsewhere.

 

<quote>

 

Max(Len(CollectionName.ColumnName),Result)

And then use the following formula to judge if a string length is the longest. 

Len("TheString") > Max(Len(CollectionName.ColumnName),Result)

</quote>

 

With respect to the situation I am trying to solve above, unfortunately this also does not solve the issue since it only considers whether the string is the longest of all strings contained within the collection. I need to filter the strings so that only the strings containing the current row's string are considered for length measurement.

 

I tried to use a filter to do this, but it kept returning table values which I therefore could not use len() to wrap around.

 

How do I get my syntax correct for this situation?

 

Thanks! 

 

 

 

 

Helpful resources

Announcements
thirdimage

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors
Users online (6,738)