Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 !!!!
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 !!!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |