Forum Discussion

msantillan's avatar
msantillan
Helper II
6 years ago
Solved

Replace "null" values from ALL columns into null values Power Query

Hi, is there a way to replace values with "null" from ALL columns into NULL or Blank values in M query?

 

Something like this but applied to all of the columns:

 

Table.ReplaceValue(Source,"null",null,Replacer.ReplaceValue,{ALL COLUMNS})

 

Thanks

Need Help
  • ibarrau's avatar
    ibarrau
    6 years ago

    Hi. Yes you can. Try something like this:

    = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue, Table.ColumnNames(#"Step with all colums") )

    You can list the columns of one step in order to do the change. This will replace "" (blanks) with null.

     

    Regards,

  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi msantillan ,

     

    Press Ctrl+A select all column and right the table then use replace value feature.

    You can check the query through Advance Editor feature.

     

    Best Regards,

    Jay

     

    Community Support Team _ Jay Wang

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

5 Replies

  • ibarrau's avatar
    ibarrau
    Super User
    6 years ago

    Hi. Yes you can. Try something like this:

    = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue, Table.ColumnNames(#"Step with all colums") )

    You can list the columns of one step in order to do the change. This will replace "" (blanks) with null.

     

    Regards,

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi msantillan ,

     

    Press Ctrl+A select all column and right the table then use replace value feature.

    You can check the query through Advance Editor feature.

     

    Best Regards,

    Jay

     

    Community Support Team _ Jay Wang

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

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago
    So if you are saying ALL COLUMNS and do not mean specifying each column individually, I'm not sure you can do that, but ImkeF or edhans would probably know for sure.
      • ImkeF's avatar
        ImkeF
        Community Champion
        6 years ago

        Like edhans said.

        The "#"Step with all colums" is probably "Source" here for your case.

        (It's mostly the first argument of the Table.ReplaceValue-function for these use cases)