Forum Discussion
Solved
How to replace a value in multiple columns when the columns have to be dynamically selected?
I need to replace a value in a set of columns (in this case null value with 0), but I cannot hard-code the columns. Instead, I need to determine the columns dynamically: only columns with a specific ...
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], FilteredList = List.Buffer(List.Select(Table.ColumnNames(Source), each Text.StartsWith( _ , "Invoiced_"))), #"Replaced Value" = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue, FilteredList) in #"Replaced Value"
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
FilteredList = List.Buffer(List.Select(Table.ColumnNames(Source), each Text.StartsWith( _ , "Invoiced_"))),
#"Replaced Value" = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue, FilteredList)
in
#"Replaced Value"
Wow, this worked. Thank you so much!
If I'm reading this right, the trick was to create a new List instance, right?
guess your code error came from the reference of Table1,
i just changed it to Source