cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
daneomite Frequent Visitor
Frequent Visitor

Unpivot - replace values dynamically for additional columns OR keep nulls

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

1 ACCEPTED SOLUTION

Accepted Solutions
daneomite Frequent Visitor
Frequent Visitor

Re: Unpivot - replace values dynamically for additional columns OR keep nulls

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 !!!!

1 REPLY 1
daneomite Frequent Visitor
Frequent Visitor

Re: Unpivot - replace values dynamically for additional columns OR keep nulls

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 !!!!