Forum Discussion
Function crashes: Operation is not valid due to the current state of the object.
Community ChampionWould 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".
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
Hi smikou ,
that error message often is a sign of a corrupted file.
So what you did to start from scratch was the solution.
Hi ImkeF
It seems that I forgot to make one of the arguments of the function variable, that's why it seemed like working... but it's not 😞
I would like to send a sample file like explained on edhans link, but the problem concerns excel files importation.
My function is simple, it merges a few excel files into one, and each time it gets data from 2 different sheets.
The arguments on this function are: the file's name, it's path and the Sheet's name.
#"File" = #"Lignes filtrées1"{[Name=FileName,#"Folder Path"=Path]}[Content],
#"Excel importé" = Excel.Workbook(#"File"),
Sheet = #"Excel importé"{[Item=Sheet,Kind="Sheet"]}[Data],The problem comes from the last parameter: Sheet.
When I replace the parameter Sheet by the real name of one of the sheets (e.g. "Sheet1" or "Sheet2"), it works just fine.
But when I tell the function to get Sheet from a column (with only "Sheet1" or "Sheet2" as values) like FileName and Path, it crashes.
