Forum Discussion

Michael_HERE's avatar
Michael_HERE
Regular Visitor
2 years ago
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 ...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    2 years ago
    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"
Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
2 years ago

Use this

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FilteredList = List.Select(Table.ColumnNames(Table1), each Text.StartsWith( _ , "Invoiced_")),
    #"Replaced Value" = Table.FromRecords(
    Table.TransformRows(Source, (r) => List.Accumulate(FilteredList, r, (s,c)=> 
        Record.TransformFields(s,{{c, each if _ = null then 0 else _}})))
        , Value.Type(Source)))
in
    #"Replaced Value"
  • Michael_HERE's avatar
    Michael_HERE
    Regular Visitor
    2 years ago

    Unfortunately, I get the same error. It occurs already in the second line. 

    Maybe I should have mentioned that I am using PowerQuery within Excel, not Power BI (if that matters). 

    Besides, the last closing parenthesis in your code is too much. 

    • Vijay_A_Verma's avatar
      Vijay_A_Verma
      Most Valuable Professional
      2 years ago

      Is it possible to post an Excel file having 2-3 rows of dummy data to Onedrive / Google drive and share the link here?

      PQ in Excel or Power BI behave the same so that won't make a difference.