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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Oceanfront
New Member

How to find unique text from when comparing 2 columns (as either a new column or measure)

Hi all,

 

I have two columns, the first column contains a list of names seperated by commas that have completed a task. The second column has all names as a list seperated by commas. I want to find which names haven't completed the task by comparing each rows list and returning the names that don't appear in the first column when compared to the second column. Additionally the returned names should be returned on the same row in "column 3" as both columns are indexed by the same time column.

 

This can not be done in M.query as the columns are generated within PowerBi and so don't appear in the initial query.

 

It should look like this:

 

DayNames completed task All names Names that haven't completed task
1Bob, Sam, Greg Bob, Sam, Greg, Tim, Paul  Tim, Paul
2Greg Bob, Sam, Greg, Tim, Paul  Bob, Sam, Time, Paul
3Tim, Paul Bob, Sam, Greg, Tim, Paul  Sam, Greg, Bob
4Sam, Tim Bob, Sam, Greg, Tim, Paul  Greg, Bob, Paul

 

Please ask any questions if you are unsure at what I am asking.

 

Thank you!

 

4 REPLIES 4
rajulshah
Resident Rockstar
Resident Rockstar

@Oceanfront ,

 

Do you want something like below? Please confirm.

rajulshah_0-1677581833796.png

 

@rajulshah Thank you for your reply, yes I do want something like this however in my actual data there are far more names than just 5. Can this be done in a way that deals with 100 values which doesnt require 100 different lines of code to search each one?

@Oceanfront ,
I have given this a lot of thought and AFAIK this is only possible using Power Query.

@rajulshah Thank you for thinking about this. I too have been trying for a long time to work this out. I'll keep thinking about it and maybe look to make it in power query if I find a way.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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