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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
LFrench
Frequent Visitor

Matching Column Values

Based on suggestions from this group, I half figured out this problem, but now I keep getting errors on one section that I can't determine how to resolve. I have column "GAP" comparing values to column "GAPs" and if the value in "GAP" appears anywhere in "GAPs," then I get a TRUE result, else FALSE. It works any time that there is a value in "GAPs," but fails if either column is blank. I attempted to put a few differant values (ie - None, OO/OO/OO, etc) to try to get a FALSE result using a REPLACE, but it was unsuccessful. I'm looking to get a FALSE result if GAPs is blank. 

 

= Table.AddColumn(#"Expanded Active Members", "Current Responder GAP", each if Text.Contains([GAPs], [GAP]) then true else false)

 

LFrench_0-1751935132020.png

 

Thanks for your assistance!

 

1 ACCEPTED SOLUTION
MasonMA
Solution Sage
Solution Sage

@LFrench 

 

Hi, Text.Contains function cannot handle Null values, as in below link.

 

https://learn.microsoft.com/en-us/powerquery-m/text-contains


You may do it through PQ UI or simply update your M code by adding a Null check: 
= Table.AddColumn(

#"Expanded Active Members",

"Current Responder GAP",
each if [GAPs] = null or [GAPs] = ""

then false
else Text.Contains([GAPs], [GAP]))

 

Hope it helps. 

View solution in original post

3 REPLIES 3
v-lgarikapat
Community Support
Community Support

Hi @LFrench ,

Thanks for reaching out to the Microsoft fabric community forum.

 

@MasonMA ,

@MattAllington ,

Thanks for your prompt response,

In addition to @MasonMA and @MattAllington, I have taken a sample dataset, implemented the scenario, and uploaded the sample PBIX file for review. Could you please take a look and share your feedback?

 

vlgarikapat_0-1751969446489.png

 

If this post helped resolve your issue, please consider the Accepted Solution. This not only acknowledges the support provided but also helps other community members find relevant solutions more easily.

We appreciate your engagement and thank you for being an active part of the community.

Best regards,
LakshmiNarayana
.

 

MasonMA
Solution Sage
Solution Sage

@LFrench 

 

Hi, Text.Contains function cannot handle Null values, as in below link.

 

https://learn.microsoft.com/en-us/powerquery-m/text-contains


You may do it through PQ UI or simply update your M code by adding a Null check: 
= Table.AddColumn(

#"Expanded Active Members",

"Current Responder GAP",
each if [GAPs] = null or [GAPs] = ""

then false
else Text.Contains([GAPs], [GAP]))

 

Hope it helps. 

MattAllington
Community Champion
Community Champion

There are various approaches. Simple is good. How about before your step that does the search for text in text, add 2 steps, 1 to replace null with X and another to replace blank with X. Then it should work



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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

Top Solution Authors