Unpivot - replace values dynamically for additional columns OR keep nulls

[daneomite]

In Query Editor, before an unpivot, how can I account for new columns of data being added when using the replace values “Null” with 0?  I’ve found that replace values does not pick up new columns when added to the table, thus, the unpivot removes the new column's rows with null values. Is there a way to make the replace values dynamic as new columns are added OR to keep nulls through the unpivot process and then replace them with 0's once all the values, including nulls, are in a single column?

I've only been able address this issue by manually adding the column name in the advanced editor's replace values code line.

Thanks,

Dane

[daneomite · Accepted solution]

Col becomes the list of all column names (dynamic)… ColList is then all but the first column and then that is provided into the last step that replaces your values…
let
Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
Col = Table.ColumnNames(Source),
ColList=List.Skip(Col, 1),
#”Replaced Value” = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue,ColList)
in
#”Replaced Value”

A big thank you to Gašper Kamenšek @ExcelUnplugged !!!!