Search code examples
excelpowerquerym

Unexpected error: Operation is not valid due to the current state of the object

I am getting the above error when I am trying to run this function on a text column in power query[Excel]. The column contains different names and this function checks if the entry has either of "AbbVie", "Roche" or "Pfizer" in it. It returns the names from the list that is present in the entry.

    (txt as text) =>
[
   
   create_val = (val as text,check as text, output as text) =>
     let 
       output = if Text.Contains(val,check)
       then Text.Combine({output, check},",")
       else output
     in
       output,
    final_value = List.Accumulate({"AbbVie","Roche","Pfizer"},"",(state,current) => create_val(txt,current,state))
][final_value]
Solution

  • I struggled with this one too so I though I'd share how I solved it.

    I was using a custom function recursively, as a means to implement looping. It turns out I was modifying variables from the function definition, i.e. variables that the function had been called with. By declaring local variables, the function worked as expected.