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 _ ), " ") ),

      • MarcelBeug's avatar
        MarcelBeug
        Community Champion
        9 years ago

        Thanks for the thumbs up, but I'm really confused by your previous post.

        Your question was about replacing null values, so what have column headers to do with that??

        Is your initial question answered?

        Is your previous post just a remark or a new question?

        In the latter case you'd better mark this topic as answered and raise a new topic.