Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 can't figure out how to exclude all the fixed columns while also including any new ones that import. 

 

On previous steps, I could simply say if (#"Promoted Headers"<>{"Department Code","Department Name","Employee Name","Pay Type"}) then whatever step I need. 

 

It's not working for the replace value step currently (I could be doing something wrong). 

 

Any help is appreciated! 

2 ACCEPTED SOLUTIONS
edhans
Super User
Super User

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

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

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

View solution in original post

5 REPLIES 5
edhans
Super User
Super User

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

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

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

@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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

My code is working on the fake data in the Source line of my code sample. You need to take my steps after that and apply it to your excel file. Basically the Core Columns and later steps. 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors