cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jowiko
Advocate III
Advocate III

Discovering orphaned data in a single table

Here is a challenging one.

 

Consider a table that has a "Title" column and a "Second" column that uses the first as a lookup, however the lookup is not enforced nor is it an actual lookup, it's just text.  Sometimes there is data in the Second column that does not exist in the Title column, I want to list those values out, however SharePoint does not allow for a CountIf function in the calculated column, so I'll have to resort to a CountIf in PowerApps.  

 

What I came up with so far:

 

I tried this first in a gallery: Filter(DataStore,CountIf(DataStore,Title=Second)<=0)

 

But that did not work very well, it left blanks, so I tried this one:

Filter(SortByColumns(AddColumns(DataStore, "Orphan", CountIf(DataStore,(TrimEnds(Upper(Title))=TrimEnds(Upper(Second))))),"Orphan",Ascending),Orphan=0)

 

With this one, if I invert the comparator, the Orphan count is the same for all the results, while the results are not orphans.  I think I am not doing this right...

1 ACCEPTED SOLUTION

Accepted Solutions
dinusc
Microsoft
Microsoft

Just to reiterate the problem you're trying to solve, You have a list that (among other data) has 2 text columns: "Title" and "Second". In some cases, the "Second" field has values that are also contained in the "Title" column. You would like to show all the records in which the "Second" field has a value that does not exist anywhere in the "Title" column. The following formula should do that:

Filter(DataStore, Not(Second in DataStore.Title))

If you also want to filter out all the empty values, then:

Filter(DataStore, !IsBlank(Second ) And Not(Second in DataStore.Title))

You can also add Trim function to remove all the leading/ending spaces before checking if the value is empty.

Hope this helps.

View solution in original post

2 REPLIES 2
dinusc
Microsoft
Microsoft

Just to reiterate the problem you're trying to solve, You have a list that (among other data) has 2 text columns: "Title" and "Second". In some cases, the "Second" field has values that are also contained in the "Title" column. You would like to show all the records in which the "Second" field has a value that does not exist anywhere in the "Title" column. The following formula should do that:

Filter(DataStore, Not(Second in DataStore.Title))

If you also want to filter out all the empty values, then:

Filter(DataStore, !IsBlank(Second ) And Not(Second in DataStore.Title))

You can also add Trim function to remove all the leading/ending spaces before checking if the value is empty.

Hope this helps.

View solution in original post

Interestingly, the first one did not work, but the second one did.  Thanks so much for this! 🙂

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,437)