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

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

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.

2 REPLIES 2
Microsoft Employee

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.

Jowiko
Level: Powered On

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! Smiley Happy

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 51 members 4,852 guests
Please welcome our newest community members: