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

[Anonymous]

Hi, 

I get this error
Unexpected error: Operation is not valid due to the current state of the object.
Details:
Microsoft.Mashup.Evaluator.Interface.ErrorException: Operation is not valid due to the current state of the object. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Operation is not valid due to the current state of the object. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Operation is not valid due to the current state of the object. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Operation is not valid due to the current state of the object. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Operation is not valid due to the current state of the object. ---> System.InvalidOperationException: Operation is not valid due to the current state of the object. ---> System.InvalidOperationException: Operation is not valid due to the current state of the object.
at Microsoft.Mashup.Engine1.Language.Compiler.ToFunction(IFunctionExpression expression)
at Microsoft.Mashup.Engine1.Runtime.CollapseNestedFunctionsVisitor.VisitFunction(IFunctionExpression node)
at Microsoft.Mashup.Engine.Ast.AstVisitor2.VisitExpression(IExpression expression)

when calling a function: 
(Ref as table, Org as table) =>
let
 RefOC = Table.AddColumn
  (
   Ref,
   "Affectation.new",
    each Entité_parent_seuil([Affectation], Org) // on recherche l'entité parent niveau n-1 dans 'Org' (liste des entités)
  )
in
RefOC

where [Affectation] is a text column in table 'Ref'
and 'Entité_parent_seuil(Entite, Org)' is a function with 2 parameters : text and table
I checked this function returns expected result when calling it with a fixed text string (instead of ' [Affectation] ')

I did not find the right weiting for such call , can you help ?

[OmarTorres]

I hope this helps someone but in my case this was resolved after I apply the function "Text.From" once I called the column in the invoke function . Also as others I tried to invoke the function with static strings.

First I tested the funtion as it is:

=CustomInvokedFunction("Mexico","2023-06-30","Omar")

Then I tried to iterate for each row with also STATIC VALUES.

= Table.AddColumn(#"Filtered Rows", "CustomColum", each CustomInvokedFunction("Mexico","2023-06-30","Omar"))

Then try to call the column (this is when it was crashing):

= Table.AddColumn(#"Filtered Rows", "CustomColum", each CustomInvokedFunction([Site],[Date],[Name]))

Then I changed with the Text.From function (my solution):

= Table.AddColumn(#"Filtered Rows", "CustomColum", each CustomInvokedFunction(Text.From([Site]),Text.From([Date]), Text.From([Name])))

  I dont know why power by doesnt identify the column called as text. But for me this worked!!

[Anonymous]

I also checked that 

(Ref as table, Org as table, seuil as number) =>
let
RefOC = Table.AddColumn
(
Ref,
"Affectation.new",
(x)=>x[Affectation]
)
in
RefOC

does not return any error. It confirms the pb is in the call to the function Entité_parent_seuil()
although this function runs correctly when tested independantly

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

I can reproduce your problem using the following custom functions.

Then I can fix the problem by modifying the code of the Entité_parent_seuil function.

(P_Entite as text, Tab as table) =>

let
    Source = Table.Contains(Tab, [Entite = P_Entite])
in
    Source

The conclusion seems to be that Power Query prevents us from using the reserved keyword to create the parameter names of the custom function.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

you may notice that the test related to NBUser is mentioned but not implemented in this version (just to make it simpler)

[Anonymous]

Here is the full code : 

(Ref as table, Org as table) =>
let
RefOC = Table.AddColumn
(
Ref,
"Affectation.new",
(x)=> Entité_parent_seuil(x[Affectation], Org) // on recherche l'entité parent niveau n-1 dans 'Org' (liste des entités)
)
in
RefOC

(Affectation as text, Org as table) => // recherche l'entité parent dans la table des entités 'Org' telle que le NBUser est > seuil min 'NbUserMin'
let
Affectation = Text.BeforeDelimiter(Affectation, "/", {0, RelativePosition.FromEnd}),
row =
if Affectation = null then null else
if Table.SelectRows(Org, each [Entite] = Affectation) <> null then Table.SelectRows(Org, each [Entite] = Affectation)
else @Entité_parent_seuil(Affectation, Org) ,
row1 = Table.First(row)[Entite]
in
row1

this function is looking for lines in List_entite that have  'Entité'  = text in Affectation before delimiteur /

[Vijay_A_Verma]

Try with below code

(Ref as table, Org as table) =>
let
 RefOC = Table.AddColumn
  (
   Ref,
   "Affectation.new",
    (x)=> Entité_parent_seuil(x[Affectation], Org) // on recherche l'entité parent niveau n-1 dans 'Org' (liste des entités)
  )
in
RefOC

[Anonymous]

Thx Vijay

when writing ' (x) ' as 'Entité_parent_seuil()' parameter , x is taking first argument of the funtion as value (= Ref) in present case, correct?

I just tried your code, I have the same error.

[Vijay_A_Verma]

Can you post the full query as well function query - Entité_parent_seuil?