Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Replace "null" excluding certain columns

I have payroll source file that will add a new column for each passing day. There is a core set of columns that will remain constant. I want to update all null values in the non-core columns to 0. I ...
  • edhans's avatar
    6 years ago

    Try this code:

     

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUoC4mQgNgRipVidaKUUICMViNNAAjpKRkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, xx1 = _t, xx2 = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"xx1", Int64.Type}, {"xx2", Int64.Type}}),
        #"Core Columns" = {"Column1", "Column2", "Column3"},
        #"Dynamic Columns" = List.Difference(Table.ColumnNames(#"Changed Type"),#"Core Columns"),
        #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue, #"Dynamic Columns")
    in
        #"Replaced Value"

     

     

     

    What it does:

    1. Creates a list of core columns. You will need to modify that list manually for your core columns.
    2. The Dynamic Columns step is the list of all columns less your Core columns
    3. The replace function uses the Dynamic Column list.

    NOTICE: These steps are not sequential. Both Dynamic Columns and Replaced Values refer all the way back to the #"Changed Type" table, so be careful when editing, and if you move steps with the mouse, Power Query may try and change your hand-typed table references and bork it up, so you'd need to fix manually again.

     

     

    1) In Power Query, select New Source, then Blank Query
    2) On the Home ribbon, select "Advanced Editor" button
    3) Remove everything you see, then paste the M code I've given you in that box.
    4) Press Done

     

  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    As tested, edhans's answer works.

    please paste the following code under your exsiting query,

       your steps:
       step1=***,
       step2=***,
       your_own_last_step_name=***,
       #"Core Columns" = {"your column name1", "your column name2", "your column name3"},
       #"Dynamic Columns" = List.Difference(Table.ColumnNames(your_own_last_step_name),#"Core Columns"),
       #"Replaced Value" = Table.ReplaceValue(your_own_last_step_name,null,0,Replacer.ReplaceValue, #"Dynamic Columns")
    in
        #"Replaced Value"

     

    Best Regards

    Maggie