Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Replace blank value

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.

6 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional

    Select that column - Transform menu - Format - Clean.

    Now try to replace blank

    • Anonymous's avatar
      Anonymous
      Not applicable

      It's not working.

      • Vijay_A_Verma's avatar
        Vijay_A_Verma
        Most Valuable Professional

        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?

  • 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's avatar
      Anonymous
      Not applicable

      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's avatar
        v-yanjiang-msft
        Community Support

        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.