Forum Discussion
Custom Function works, but not when invoked with column names
I have written a custom function, that works perfectly when passed values directly, but when invoking it on my table and passed the same information via table columns says "Unexpected error: Operation is not valid due to the current state of the object."
The function is:
(varTotalLoops as number, varLoopNumber as number, varCurrentValue as text, varEndValue as text) =>
let
varCurrentLoop = varLoopNumber +1,
varEndValue = varEndValue & Text.From(Number.From(varCurrentValue)-1) & "/" & varCurrentValue & ",",
varCurrentValue = Text.From(Number.From(varCurrentValue)+1),
output =
if varCurrentLoop >= varTotalLoops
then Text.Start(varEndValue, Text.Length(varEndValue)-1)
else @testFunction1(varTotalLoops, varCurrentLoop, varCurrentValue, varEndValue)
in
output
When I run this code with hard coded values, I get the correct output;
But when I run this on a table with one row which has the same values, I get this;
I thought it might be a type mismatch which is why i tried the seperate field for the same info but with a different data type.
Not sure why it won't work, any advice would be appriciated
Try using this version of your function instead. It avoids reusing the inputs as variables inside the function.
(inputTotalLoops as number, inputLoopNumber as number, inputCurrentValue as text, inputEndValue as text) => let varTotalLoops = inputTotalLoops, varCurrentLoop = inputLoopNumber +1, varEndValue = inputEndValue & Text.From(Number.From(inputCurrentValue)-1) & "/" & inputCurrentValue & ",", varCurrentValue = Text.From(Number.From(inputCurrentValue)+1), output = if varCurrentLoop >= inputTotalLoops then Text.Start(varEndValue, Text.Length(varEndValue)-1) else @testFunction1(varTotalLoops, varCurrentLoop, varCurrentValue, varEndValue) in outputPat
4 Replies
ppm1Solution Sage
Try using this version of your function instead. It avoids reusing the inputs as variables inside the function.
(inputTotalLoops as number, inputLoopNumber as number, inputCurrentValue as text, inputEndValue as text) => let varTotalLoops = inputTotalLoops, varCurrentLoop = inputLoopNumber +1, varEndValue = inputEndValue & Text.From(Number.From(inputCurrentValue)-1) & "/" & inputCurrentValue & ",", varCurrentValue = Text.From(Number.From(inputCurrentValue)+1), output = if varCurrentLoop >= inputTotalLoops then Text.Start(varEndValue, Text.Length(varEndValue)-1) else @testFunction1(varTotalLoops, varCurrentLoop, varCurrentValue, varEndValue) in outputPat
Hi Anonymous ,
The conclusion seems to be that Power Query prevents us from using the reserved keyword to create the parameter names of the custom function.
Here is a similar case that you can refer to: Solved: error: Operation is not valid due to the current s... - Power Platform Community (microsoft.com)
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.