Identify if cell contains repeated words

gk2go · ‎11-16-2019

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 · ‎11-17-2019

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: