Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Replace multiple nulls at once in Power Query

I've got a table with a few columns of numerical data, and a few with text data. For all of the numerical columns, where the value is null, I want to replace those with a 0. For columns where it is t...
Need Help
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

Hi Anonymous

 

This worked for me

 

= Table.ReplaceValue(#"Renamed Columns",null,0,Replacer.ReplaceValue,{"Col 1", "Col 2", "Col 3", "Col 4"})
  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    9 years ago

    Anonymous

    The same way as Phil_Seamark but using the UI. Press "Ctrl" to select multiple columns and replace the values.

     

     

    • MarcelBeug's avatar
      MarcelBeug
      Community Champion
      9 years ago

      A more dynamic approach below. I splitted the solution in separate steps just for clarity.

      Input is in Table1

       

      Query TableShema:

       

      Table.Schema(Table1)

       

      Query NumberColumns returns a list of number columns in Table1: 

       

      let
    Source = TableSchema,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Kind] = "number")),
    Name = #"Filtered Rows"[Name]
in
    Name

       

      Query TextColumns returns a list of text columns in Table1:

       

      let
    Source = TableSchema,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Kind] = "text")),
    Name = #"Filtered Rows"[Name]
in
    Name

       

      Query Result replaces the values in Table1:

       

      let
    Source = Table1,
    #"Replaced Value" = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue,NumberColumns),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"Undefined",Replacer.ReplaceValue,TextColumns)
in
    #"Replaced Value1"

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        MarcelBeug, this is much closer to what I had envisioned with some of the capabilities. I came across something similar to what I was thinking, but this was only to change a column header. It's just a step you add anywhere in your power query to transform the headers to a clean name, regardless of what's there. I think something like this is possible with the data types, but it might be a lot longer given the work needed for figuring out the Kind.

         

         

        #"Alter Column Names" = Table.TransformColumnNames( #"<<<Previous Change Name>>>", ( columnName as text ) as text => Text.Combine( List.Transform( Text.Split( columnName, "_" ), each if Text.Length( _ ) >= 4 then Text.Proper( _ ) else _ ), " ") ),