Discovering orphaned data in a single table

[Jowiko]

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...

[dinusc · Accepted solution]

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]

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