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.

View solution in original post

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.

View solution in original post

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! 🙂

Helpful resources

Announcements
thirdimage

Power Automate 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

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 196 members 6,262 guests
Please welcome our newest community members: