Forum Discussion
Replace "null" excluding certain columns
- 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:
- Creates a list of core columns. You will need to modify that list manually for your core columns.
- The Dynamic Columns step is the list of all columns less your Core columns
- 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 - 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
Hi edhans,
Thanks so much! I used this and I'm not getting an error, but the data on the intended source (excel import) isn't replacing nulls.
I understand how everything but the source code - is there anything I should be changing? I'm not sure how it's reading data from the intended sheet and able to handle the unknown number of columns. For reference, I thought maybe I needed to change the source to the sheet/source name, but that didn't work and gave the "Could not find Column 'xx1'".
Thanks again!
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
- edhans6 years agoCommunity Champion
Anonymous if you can post the query you want my code to work against, please do so. I will append my steps to your query and give you the whole thing back. I am writing an article to show people how to do this (I take some things for granted!) but it isn't ready yet and I don't want to delay your project.