If the answer to your question in the forum involves Power Query, or can be best done in Power Query, the proposed solution may come in the form of a block of code, known as M Code, and may look like the following:
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"
To see how this works, you just paste that code to the Power Query Advanced Editor in a new blank query.
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
Let's look at the original question, then what that brief snippet of code does, then how to use that code in your model with real data, not sample data.
The original question was how can you replace all of the null values in new columns with zero, but not replace nulls in the first three columns? Every month a new column is added to the data, probably an Excel or CSV file, so the code has to handle these new columns.
Let's break down the above code. The first two lines are the sample data to have something to work with. It looks like gibberish, but that is what Power Query uses when you use the "Enter Data" function. It stores that data in a binary format. So, these first two rows of code return this small table in Power Query:
Now we have some sample data. Column1, Column2, and Column3 represent the core columns that should never have the any nulls, should they appear, replaced with zero. But columns xx1 and xx2 should have nulls replaced with zero, and per the request, future columns xx3, xx4, xx5, etc. should also have the nulls replaced.
The last three steps, which are called Core Columns, Dynamic Columns, and Replaced Values are what you need to append to your original query. Let me briefly explain what each step does to provide some context:
Let's assume your original query is importing a CSV file. It might look something like this after the initial "Get Data from CSV" operation:
let
Source = Csv.Document(File.Contents("C:\Users\User Name\OneDrive\Test File.csv"),[Delimiter=",", Columns=5, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"xx1", Int64.Type}, {"xx2", Int64.Type}})
in
#"Changed Type"
What we want to do is take the last three steps from the first query at the top of this article and graft them to this query. For now, just paste those last three steps to your data in the Advanced Query editor. it will look like this:
Now let's clean it up, because as is, it will not work.
That's it. You can press DONE in the advanced editor.
This all assumes the column names you gave us to work with in your sample data match up. If not, you have some more editing to do, but this will at least get the critical code integrated with your source data.
For an even deeper dive on how to incorporate M code from the forums into your code, please see this video by @ImkeF.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.