Text.Contains isn't working with blanks and nulls

[Anonymous]

I'm importing an incident spreadsheet into Power BI and there's a column ("Flag") used to indicate a particular condition The field in Excel appears contain either 'Y' or be blank, but on import to Power BI I see it's either 'Y', null or blank.

I wanted a neat solution to translate it to either 'Yes' or 'No' (for use in a slicer) and came up with this:

= Table.ReplaceValue(#"Prev Step", each [Flag], each if Text.Contains([Flag], "Y") then "Yes" else "No",Replacer.ReplaceText,{"Flag"})

This translates 'Y' to 'Yes' but has no effect on the other values and I've had to add 2 more steps to replace "" with "No" and null with "No". 

I tried to reverse the logic using:

= Table.ReplaceValue(#"Prev Step", each [Flag], each if not Text.Contains([Flag], "Y") then "No" else "Yes",Replacer.ReplaceText,{"Flag"})

Which again converts "Y" to "Yes" but leaves nulls and blanks untouched.

It appears that 'M' has a bit of a blind spot with nulls and spaces - am I doing something wrong or does Test.Contains never match with nulls and blanks?

Edit: This is a workaround which works and is very similar logic:

= Table.AddColumn(#"Create INC URL", "Flag2", each if [Flag] = "Y" then "Yes" else "No", type nullable text)

[diogofdasilva]

Use 

if (Text.Contains([Flag], "Y")??false) then "Yes" else "No"

[Ghhousuddin]

You're correct that Text.Contains does not work well with nulls and blanks. Instead of using Text.Contains, you can use the try...otherwise construct in Power Query M language to handle nulls and blanks.

Here's a modified version of your workaround that will handle nulls, blanks, and 'Y':

= Table.AddColumn(#"Prev Step", "Flag2", each if try Text.StartsWith([Flag], "Y") otherwise false then "Yes" else "No", type nullable text)

This code will attempt to check if the 'Flag' column starts with "Y" using Text.StartsWith. If it encounters a null or blank value, the try...otherwise construct will return false. If the result is true (meaning the 'Flag' value is "Y"), it will return "Yes", otherwise it will return "No". This should handle all three cases: 'Y', null, and blank.

You can replace "Prev Step" with the actual name of the previous step in your Power Query.

[Anonymous]

Hi @Anonymous ,

As far as I know, Power BI will show empty data in number format by null and show show empty data in text format by nothing.

If you want to replace null value or use null value in M code, you need to use find null.

If you want to replace text nothing value , you can write nothing to find it. 

And you need to use "" to find it in M Code.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

The two steps you gave me to replace null with a value and "" with a value I had already mentioned (I've had to add 2 more steps to replace "" with "No" and null with "No". ).

I created some smaple data as follows:

'Flag' is the original column with values being either 'Y', space or empty (null). I duplicated the colum to create 'Old flag' and then duplicated that to create 'Flag with spaces' and change spaces to <space> and blanks to <null> so you can see the original data.

Then I added a step :

= Table.ReplaceValue(#"Replaced Value1", each [Flag], each if Text.Contains([Flag], "Y") then "Yes" else "No",Replacer.ReplaceText,{"Flag"})

which resulted in the 'Flag' column you can see. This does appear to have changed the rows containing spaces to 'No' but has again left the null columns unaffected. 

finally I added the following step:

= Table.AddColumn(#"Transform Flag to Yes or No", "Flag2", each if [Original Flag] = "Y" then "Yes" else "No", type nullable text)

Which works as expected, translating 'Y' to 'Yes' and everything else to 'No'.

This quite clearly shows how AddColum and ReplaceValue handle nulls differently. 

Here is the complete Power Query:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilSK1YGRCmASiamAIYmhEIuWWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Flag = _t]),
#"Duplicate flag column" = Table.AddColumn(Source, "Original Flag", each [Flag]),
#"Added Custom" = Table.AddColumn(#"Duplicate flag column", "Flag with spaces", each [Original Flag]),
#"Replaced Value" = Table.ReplaceValue(#"Added Custom"," ","<space>",Replacer.ReplaceText,{"Flag with spaces"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","","<null>",Replacer.ReplaceValue,{"Flag with spaces"}),
#"Transform Flag to Yes or No" = Table.ReplaceValue(#"Replaced Value1", each [Flag], each if Text.Contains([Flag], "Y") then "Yes" else "No",Replacer.ReplaceText,{"Flag"}),
Custom1 = Table.AddColumn(#"Transform Flag to Yes or No", "Flag2", each if [Original Flag] = "Y" then "Yes" else "No", type nullable text)
in
Custom1

  You just need to create some suitable sample data.