cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Microsoft
Microsoft

Re: Discovering orphaned data in a single table

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
Highlighted
Microsoft
Microsoft

Re: Discovering orphaned data in a single table

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

Highlighted
Advocate III
Advocate III

Re: Discovering orphaned data in a single table

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

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (10,452)