Replace "null" excluding certain columns

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

 

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