Create column of missing text parts comparing two columns

[thschr]

Hi everyone,

I am trying to tackle following problem: Let's say I have two columns [words1] and [words2] with longer texts as string values.

For example: 

row of words1: "magic dog food xxl"

row of words2: "dog food"

How can I create a column that return each word of words1 that is not part of words2?

For this case the row value for words3 would be: "magic xxl"

I've tried vlookup and Text.Contains separating the texts into single columns, but didn't come to any solution so far. 

Thanks in advance!

Best regards

[Greg_Deckler]

So, I've been thinking about this and I don't see a solution in DAX honestly. What I would suggest would be to split out your sentences in Query Editor and unpivot them. What you want to do would be to end up with something like this:

Row, Category, Word

1, word1, magic

1, word1, dog

1, word1, food

1, word1, xxl

1, word2, dog

1, word2, food

2,...

At this point, the problem becomes trivial to use VAR to create a couple temp tables in a measure and use EXCEPT.

Let me know if you want me to mock this up for you or if you think you can handle it from here.

[Greg_Deckler]

Well, in that particular case, you could use:

Column = SUBSTITUTE([words1],[words2],"")

But, I imagine that the words will not always be in order so I'll look at that.