Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
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:
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @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
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:
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingMy 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting