Forum Discussion

smikou's avatar
smikou
Regular Visitor
6 years ago

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

Hello everybody,

 

After several hours of struggling and looking on the net for answers, I come here to your help.

I have built a function with three variables that outputs a table. The function works well when I enter the parameters manually (a file name, a path and a sheet name), but when I make one of the parameters read from a columun, everything crashes :

 

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. ---> 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.
à Microsoft.Mashup.Engine1.Language.Compiler.ToFunction(IFunctionExpression expression)
à Microsoft.Mashup.Engine1.Runtime.CollapseNestedFunctionsVisitor.VisitFunction(IFunctionExpression node)
à Microsoft.Mashup.Engine.Ast.AstVisitor2.VisitExpression(IExpression expression)
à Microsoft.Mashup.Engine.Ast.AstVisitor2.VisitInvocation(IInvocationExpression invocation)
à Microsoft.Mashup.Engine.Ast.AstVisitor2.VisitExpression(IExpression expression)
à Microsoft.Mashup.Engine.Ast.AstVisitor2.VisitListElements(IExpression[] list)

 

Please help me solve this problem if you have any tips to share.

Need Help

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Would to understand your code from Advanced Editor. ImkeF and edhans are some of the smartest Power Query people I know, they might have some ideas.

     

    You could check the Issues forum here:

    https://community.powerbi.com/t5/Issues/idb-p/Issues

    And if it is not there, then you could post it.

    If you have Pro account you could try to open a support ticket. If you have a Pro account it is free. Go to https://support.powerbi.com. Scroll down and click "CREATE SUPPORT TICKET".

    • smikou's avatar
      smikou
      Regular Visitor
      6 years ago

      Thank you so much for your answers Greg_Deckler and edhans  . 

      I looked for answers on the "Issues" section but nothing there, I have also opened a ticket.

       

      Today, I started from scratch (a third time) and weirdly it worked even though I basically did the same thing.

      I don't have the problem anymore with the new file, but it will help understanding the problem.

       

      Here is my function's code :

      (FileName as text, Path as text, SheetName as text) as table =>
      let
      Source = SharePoint.Files("https://xxxx.sharepoint.com/sites/Sales/", [ApiVersion = 15]),

      // The file name and the path are the 2 first parameters
      #"Fichier" = Source{[Name=FileName,#"Folder Path"=Path]}[Content],
      #"Excel importé" = Excel.Workbook(#"Fichier"),

      // the last parameter is SheetName
      Sheet = #"Excel importé"{[Item=SheetName,Kind="Sheet"]}[Data],
      #"Premières lignes supprimées" = Table.Skip(Sheet,20),
      #"En-têtes promus" = Table.PromoteHeaders(#"Premières lignes supprimées", [PromoteAllScalars=true]),
      #"Autres colonnes supprimées" = Table.SelectColumns(#"En-têtes promus",{"Année", "Commerciales", "BU", "Marché", "Q1", "Q2", "Q3", "Q4", "Total"}),
      #"Rempli vers le bas1" = Table.FillDown(#"Autres colonnes supprimées",{"Année"}),
      #"Rempli vers le bas" = Table.FillDown(#"Rempli vers le bas1",{"Commerciales", "BU"}),
      #"Valeur remplacée" = Table.ReplaceValue(#"Rempli vers le bas",null,0,Replacer.ReplaceValue,{"Q1", "Q2", "Q3", "Q4", "Total"}),
      #"Type modifié1" = Table.TransformColumnTypes(#"Valeur remplacée",{{"Q1", type number}, {"Q2", type number}, {"Q3", type number}, {"Q4", type number}, {"Total", type number}}),
      #"Colonnes supprimées" = Table.RemoveColumns(#"Type modifié1",{"Total"}),
      #"Tableau croisé dynamique des colonnes supprimé" = Table.UnpivotOtherColumns(#"Colonnes supprimées", {"Année", "Commerciales", "BU", "Marché"}, "Attribut", "Valeur"),
      #"Derniers caractères extraits" = Table.TransformColumns(#"Tableau croisé dynamique des colonnes supprimé", {{"Attribut", each Text.End(_, 1), type text}}),
      #"Type modifié" = Table.TransformColumnTypes(#"Derniers caractères extraits",{{"Attribut", Int64.Type}}),
      #"Personnalisée ajoutée" = Table.AddColumn(#"Type modifié", "Quarter", each #date([Année],1+(([Attribut]-1)*3),1)),
      #"Colonnes permutées" = Table.ReorderColumns(#"Personnalisée ajoutée",{"Année", "Commerciales", "BU", "Marché", "Attribut", "Quarter", "Valeur"}),
      #"Colonnes supprimées1" = Table.RemoveColumns(#"Colonnes permutées",{"Année", "Attribut"}),
      #"Colonnes renommées" = Table.RenameColumns(#"Colonnes supprimées1",{{"Commerciales", "Commercial"}}),
      #"Lignes filtrées" = Table.SelectRows(#"Colonnes renommées", each [Marché] <> null)
      in
      #"Lignes filtrées"

      • ImkeF's avatar
        ImkeF
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Hi smikou ,

        that error message often is a sign of a corrupted file.

        So what you did to start from scratch was the solution.