Replace blank value

[Anonymous]

Hi,

I was trying to replace blank value with some value by using replace value command in power query but the answer is same as blank value. Before that I have tried with trim column and after that I have used replace value in power query even that didn't work. also I have tried to replace it in the m query by querying it but it wont worked. Also tried different ways in desktop but not working can anyone help.

[AlexisOlson]

There are different possible kinds of blank values. Here's an example that starts with a text column containing an empty string, null, whitespace, and "null" and shows what the replace value function should look like in each case.

let
    Source = Table.FromColumns({{"", null, " ", "null"}}, {"Text"}),
    #"Replaced Value" = Table.ReplaceValue(Source,"","EmptyString",Replacer.ReplaceValue,{"Text"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"ActualNull",Replacer.ReplaceValue,{"Text"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1"," ","Space",Replacer.ReplaceText,{"Text"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","null","TextNull",Replacer.ReplaceText,{"Text"})
in
    #"Replaced Value3"

Without seeing your specific table, I can't tell what sort of blank values you have. You can try adding a custom column to read the Unicode character(s) from your [TextColumn] like this and let me know what it returns:

Text.Combine(
    List.Transform(
        Text.ToList([TextColumn]),
        each Number.ToText(Character.ToNumber(_))
    ), ", ")

(An empty string will return an empty string, a null will throw an error, and any other character(s) should give a list of Unicode number(s).)

[Anonymous]

Tried with Concatenate  and after that used the replace value in query editor. It was empty that's why I wasn't able to replace it with any value. So I have tried to Concatenate  with "-" and then replaced that value.

[v-yanjiang-msft]

Hi @Anonymous ,

For empty, have you tried the below step which @AlexisOlson mentioned, it seems works fine.

 #"Replaced Value" = Table.ReplaceValue(Source,"","EmptyString",Replacer.ReplaceValue,{"Text"})

Best Regards,
Community Support Team _ kalyj

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

[Vijay_A_Verma]

Select that column - Transform menu - Format - Clean.

Now try to replace blank

[Anonymous]

It's not working.

[Vijay_A_Verma]

Can you share your pbix file without confidential data through Onedrive / any other file host like Google Drive, Drop Box, Box and share the link here?