How to replace null in all type of number column with -in power query

Question

I have 5 columns in power query with number type and there are null in some cells. I want to replace all those null with-. But power query did allow as - is not a value. I also have mixed number and text in last 3 columns, I want to clear all those text or number to leave it blank. I did not see such option in power query but only manually do it in excel.

Answer 1

Not clear why you would want to replace nulls with hyphens, but you can do both of your actions using the Table.ReplaceValue function. However, you will need to do this in the Advanced Editor.

Read MS HELP for the Table.ReplaceValue function to better understand the arguments. Also there are numerous online blogs about using the each statement as well as using the function in this manner.

Note that the Table.ReplaceValue function will change the column datatype to any. This is especially important when replacing the null with a hyphen as the hyphen is not a number

For example, given this table:

M Code

let

//Get data from an Excel Table
    Source = Excel.CurrentWorkbook(){[Name="Table16"]}[Content],

//set the data types
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"2", Int64.Type}, {"abc", type text}}),

//Rename columns
    #"Rename Columns" = Table.RenameColumns(#"Changed Type",{{"2", "col1"}, {"abc", "col2"}}),

    #"Replace Col2 with blank" = Table.ReplaceValue(
        #"Rename Columns",
        each [col2],
        "",
        Replacer.ReplaceValue,
        {"col2"}),

    #"Replace Col1 nulls with hyphen" = Table.ReplaceValue(
        #"Replace Col2 with blank",
        null,
        "-",
        Replacer.ReplaceValue,
        {"col1"}
    )
in
    #"Replace Col1 nulls with hyphen"

Results

If you have multiple columns to "Blank" or "hyphen" you can do it with this type of code:

...
  #"Replace number cols nulls with hyphen" = Table.ReplaceValue(
        #"Changed Type",
        null,
        "-",
        Replacer.ReplaceValue,
        {"col1","col4"}),

    #"Blank Columns" = Table.ReplaceValue(
        #"Replace number cols nulls with hyphen",
        List.Combine(Table.ToColumns(Table.SelectColumns(#"Changed Type",{"col2","col3"}))),
        "",
        (x,y,z)=>if List.Contains(y,x) then z else x,
        {"col2","col3"})
...

Note: If you want to replace nulls with hyphens using the UI, you will need to first change the datatype of that column to type text. But I don't know how to blank the mixed columns without using the Advanced Editor.

Instead of using Table.ReplaceValue to change the type any columns to null, you can also use Table.TransformColumns.

The code below will work on all columns with type number and/or type any and do the changes you requested without having to hard code the column names:

let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"col1", Int64.Type}, {"col2", type any}, {"col3", Int64.Type}, {"col4", type any}}),

//Select typed columns this way as `Table.ColumnsOfType` returns all the columns when looking for `type any`
    schema = Table.SelectColumns(Table.Schema(#"Changed Type"),{"Name","Kind"}),
    #"Number Cols" = Table.SelectRows(schema, each [Kind] = "number")[Name],
    #"Any Cols" = Table.SelectRows(schema, each [Kind] = "any")[Name],

//Blank "any" columns
    #"Blank Any" = Table.TransformColumns(#"Changed Type", List.Transform(#"Any Cols",(li)=> {li, each null})),

//Replace nulls with hyphens in Number columns
    #"Replace Nulls" = Table.ReplaceValue(
        #"Blank Any",
        null,
        "-",
        Replacer.ReplaceValue,
        #"Number Cols"),

//Optionallly change number columns to type text
    #"NumberCols to Text" = Table.TransformColumnTypes(#"Replace Nulls", List.Transform(#"Number Cols", each {_, type text}))
in
    #"NumberCols to Text"