Utilizing M Code Samples Given as Solutions in Power Query

[edhans]

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:

• Core Columns - this is a step that will create a list to tell the next step what columns to ignore. In this example, you would need to replace Column1, Column2, and Column3 with the column names you want to be left alone in your source data.
• Dynamic Columns - this step returns a list of all of the column names in your data, then using List.Difference(), compares that to the "Core Columns" list that was hard-coded above, and returns a final list of only column names not in the Core Columns. So as the source data adds new columns, all of those new columns would be in this "Dynamic Columns" list. Note: This is the first step in the sample code that references the original data, which is a table called #"Changed Type", and is what we will modify later.
• Replace Values - this simply replaces null with zero for all columns returned by the Dynamic Columns list.

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.

1. Delete lines 5 and 6. Those are the last steps of your original query and no longer needed.
2. Add a comma to the end of line 4, so it would end as ...{"xx2", Int64.Type}}), - this tells Power Query there are more steps to come.
3. You can ignore the #"Core Columns" step. It is ok as is and should not be changed. It is referenced later in the query, but it doesn't actually reference a previous step.
4. Line #8 possibly needs to be changed. In this particular case, it doesn't, because the last step of the real query as well as the last step of the sample code was #"Changed Step". But if your last step before we pasted this query was #"Unpivoted Columns" for example (or whatever would be on your line number 4 in the image above) needs to replace the table reference in line 8. So, the change would be as follows:
   1. #"Dynamic Columns" = List.Difference(Table.ColumnNames(#"Changed Type"),#"Core Columns"),
   2. #"Dynamic Columns" = List.Difference(Table.ColumnNames(#"Unpivoted Columns"),#"Core Columns"),

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.