I have a PowerQuery flow in Excel that needs to work on a number of dynamic column headings - so far not to much of a challenge but I now need to deal with a variable number of columns. Basically I am pivoting some data by month and want to have this increase each month.

As part of this step I need to replace the Nulls with 0 so I can do some further calculations but since the number of columns will increase I can not just use my current approach of referencing the column number as that will grow over time.

Is anyone able to nudge me the right way on this on how to deal with X columns. My current replace code is below:

= Table.ReplaceValue(#"Pivoted Column",
null, 0,
Replacer.ReplaceValue,
{Table.ColumnNames(#"Pivoted Column"){2},
Table.ColumnNames(#"Pivoted Column"){3}, 
Table.ColumnNames(#"Pivoted Column"){4}, 
Table.ColumnNames(#"Pivoted Column"){5}})

The next challenge will be that I then need to add in a repeating calculation of one column to the next again which will grow each month so that I can show the size of change between each month....

I have a PowerQuery flow in Excel that needs to work on a number of dynamic column headings - so far not to much of a challenge but I now need to deal with a variable number of columns. Basically I am pivoting some data by month and want to have this increase each month.

As part of this step I need to replace the Nulls with 0 so I can do some further calculations but since the number of columns will increase I can not just use my current approach of referencing the column number as that will grow over time.

Is anyone able to nudge me the right way on this on how to deal with X columns. My current replace code is below:

= Table.ReplaceValue(#"Pivoted Column",
null, 0,
Replacer.ReplaceValue,
{Table.ColumnNames(#"Pivoted Column"){2},
Table.ColumnNames(#"Pivoted Column"){3}, 
Table.ColumnNames(#"Pivoted Column"){4}, 
Table.ColumnNames(#"Pivoted Column"){5}})

The next challenge will be that I then need to add in a repeating calculation of one column to the next again which will grow each month so that I can show the size of change between each month....

• excel
• powerquery

Share Improve this question Follow asked Jan 17 at 17:42 user3644997user3644997 9911 gold badge11 silver badge1010 bronze badges 1

• So this has a few things. If you're pivoting a column into individual columns, you may be able to make the replacement at that stage so when it pivots into columns it won't have duplicates. I imagine the count of your columns are dynamic because you're pivoting another column. – Mark S. Commented Jan 17 at 18:50

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

try creating a transform them applying it against all the columns.

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Columns = Table.ColumnNames(#"Source"),
Fix = List.Transform(Columns,(x)=>{x, each _ ?? 0, type number}),
Transform = Table.TransformColumns(Source, Fix)
in Transform

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSgWOlWJ1oJRgbRIH4RtgE4JqQVUCMiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}}),

    Cols = Table.ColumnNames(#"Changed Type"),

    Transformed = List.Accumulate(Cols, #"Changed Type", (s,c) => Table.TransformColumns(s, {c, each _ ?? 0}))
in
    Transformed

Try to tweak the step "Cols" to pick columns to transform.