Matching Column Values

[LFrench]

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)

Thanks for your assistance!

[v-lgarikapat]

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?

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 · Accepted solution]

@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]

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