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 name pattern (e.g. starting with "Invoiced_") shall be considered.

 

I tried to pass the list of columns to the ReplaceValue function, but somehow my code doesn't work.  

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FilteredList = List.Select(Table.ColumnNames(Table1), each Text.StartsWith( _ , "Invoiced_")),
    #"Replaced Value" = Table.ReplaceValue(Table1,null,0,Replacer.ReplaceValue, FilteredList)
in
    #"Replaced Value"

 

 

I get this error: 

Expression.Error: A cyclic reference was encountered during evaluation.

 

What did I do wrong? Or how can I solve this problem? 

Thanks  

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"

6 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    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"
    • Michael_HERE's avatar
      Michael_HERE
      Regular Visitor
      2 years ago

      Wow, this worked. Thank you so much! 
      If I'm reading this right, the trick was to create a new List instance, right? 

      • wdx223_Daniel's avatar
        wdx223_Daniel
        Community Champion
        2 years ago

        guess your code error came from the reference of Table1,

        i just changed it to Source

  • 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.