Forum Discussion

daneomite's avatar
daneomite
Regular Visitor
9 years ago
Solved

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 whe...
Need Help
  • daneomite's avatar
    daneomite
    9 years ago

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

daneomite's avatar
daneomite
Regular Visitor
9 years ago

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