Identify if cell contains repeated words

[gk2go]

This post describes the scenario correctly but provides a VBA solution:

https://trumpexcel.com/duplicate-text-strings/

How to get the same in Power BI? 

I want to add a TRUE/FALSE column next to ADDRESS that is set to TRUE when ADDRESS contains a duplicate within the same cell (see arrows below), and FALSE otherwise. 

[lc_finance]

Hi @gk2go ,

you can download my proposed solution from here.

The first step is to create a table with the addresses and possible positions for the spaces (I use the spaces as a way to separate words).

Here is the DAX formula for the table:

Words position = GENERATEALL(VALUES('Addresses'[Address]), GENERATESERIES(0,50,1))

Second step is to add a column in the new table to find the spaces (spaces indicate a new word):

starting position = 
 IF([Value]=0,1,IFERROR(SEARCH(" ",[Address],[Value]), BLANK()))

Third step we create a second table that summarizes the spaces (one row for each), and that has all the words (one row per word).

Here is the DAX formula for it:

Words = FILTER(SUMMARIZE('Words position','Words position'[Address],'Words position'[starting position]),NOT 'Words position'[starting position] = BLANK())

Fourth step is a column with all the words in this added table:

Words = 
var wordsRemaining = TRIM(RIGHT([Address],LEN([Address])-[starting position]+1))
var nextSpace = IFERROR(SEARCH(" " ,wordsRemaining),LEN(wordsRemaining))
RETURN  LEFT(wordsRemaining, nextSpace)

We are now ready to check for words that appear twice in the addresses.

Here is the formula for the check, in the Addresses table:

Double = 
var currentAddress = [Address]
var countDoubles = SUMX(VALUES('Words'[Words]),
   var currentWord = [Words]
   var numberOfTimes = COUNTROWS(FILTER('Words',[Words]=currentWord && [Address]=currentAddress))
   RETURN IF(numberOfTimes>1,1,0))

RETURN countDoubles>0

Finally, here is a screenshot:

Does this help you?

LC

Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com

[gk2go]

@lc_financewhen i add the "Words" column to Words column Power BI hangs up "Working on it". Any more efficient solution?

[lc_finance]

Hi @gk2go ,

can you share a sample Power BI file where it hangs?

Based on that, I can do some tests and see if other formulas are faster,

LC