Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
thschr
Frequent Visitor

Create column of missing text parts comparing two columns

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

2 REPLIES 2
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

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. 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.