Web connector for Excel file on sharepoint

[JoachimSA]

HI everybody,

I use the web connector to import data from an excel file stored on sharepoint (I remove the "?web=1" part at the end of the link)

Every once in a while when I work on the importet table in power query I get an error saying "the sequence does not contain data" (maybe not the excat words - translated from German ;-))
The solution is to disable "load data" from within power query and the enable it again. of course this is extremly annoying as it destroys all my relationships.

Any suggestions ?

Detailed Error Info:

Feedback Type:
Frown (Error)

Error Message:
Die Sequenz enthält keine Elemente.

Stack Trace:
System.InvalidOperationException
   bei System.Linq.Enumerable.Single[TSource](IEnumerable`1 source)
   bei Microsoft.PowerBI.Modeling.Engine.Loader.PowerQueryToModelSchemaSynchronizer.NameCalculatorMeasureInfo.<>c__DisplayClass3_0.<CreateInfos>b__1(IMeasure m)
   bei System.Linq.Enumerable.WhereEnumerableIterator`1.MoveNext()
   bei Microsoft.PowerBI.Modeling.Engine.Loader.PowerQueryToModelSchemaSynchronizer.FinalNameCalculator.GetRestrictedNames(IEnumerable`1 columns, IEnumerable`1 measures, IEnumerable`1 hierarchies)
   bei Microsoft.PowerBI.Modeling.Engine.Loader.PowerQueryToModelSchemaSynchronizer.NameCalculatorMeasureInfo.CreateInfos(IEnumerable`1 desiredMeasures, IDataModel dataModel, ITable existingTable, Func`2 hasCustomizedName, Boolean syncByLineageTag)
   bei Microsoft.PowerBI.Modeling.Engine.Loader.PowerQueryToModelSchemaSynchronizer.GetFinalNamesForTableItems(EditModelItemInfo editModelItemInfo, Boolean allowNonIdealNames, ITableInput tableInput, IDataModel dataModel)
   bei Microsoft.PowerBI.Modeling.Engine.Loader.PowerQueryToModelSchemaSynchronizer.ApplyQuerySchemaToModel(IModelInput modelInput, IExtendedModelChangeScope modelChangeScope, Boolean requireFullDataRefresh)
   bei Microsoft.PowerBI.Modeling.Engine.Loader.PowerQueryToModelLoader.ApplyQueriesToModel(IExtendedModelChangeScope modelChangeScope, ModelSchemaSyncContext modelSchemaSyncContext, DataModelSnapshot preTransactionModelSnapshot, Boolean& runRelationshipDetection, List`1& newTableColumnPairs, HashSet`1& existingTableInputs, Boolean& upgradedModel, V3UpgradeStatus& v3UpgradeStatus, List`1& tableInputsExcludedFromSync)
   bei Microsoft.PowerBI.Modeling.Engine.Loader.PowerQueryToModelLoader.<>c__DisplayClass20_0.<ApplyQueryChangesAndCreateRelationships>b__0()
   bei Microsoft.PowerBI.Modeling.Engine.Loader.PowerQueryToModelLoader.ExecuteInTransaction(IExtendedModelChangeScope modelChangeScope, Func`1 action)
   bei Microsoft.PowerBI.Modeling.Engine.Loader.PowerQueryToModelLoader.ApplyQueryChangesAndCreateRelationships(IRelationshipLoader relationshipLoader, IExtendedModelChangeScope modelChangeScope, ModelSchemaSyncContext modelSchemaSyncContext)
   bei Microsoft.PowerBI.Modeling.Engine.Loader.PowerQueryToModelLoader.ProcessLoadInputs(IRelationshipLoader relationshipLoader, IExtendedModelChangeScope modelChangeScope, ModelSchemaSyncContext modelSchemaSyncContext)
   bei Microsoft.PowerBI.Modeling.Engine.Loader.PowerQueryToModelLoader.<>c__DisplayClass16_0.<PerformLoadAsync>b__3()
   bei System.Threading.Tasks.Task`1.InnerInvoke()
   bei System.Threading.Tasks.Task.Execute()
--- Ende der Stapelüberwachung vom vorhergehenden Ort, an dem die Ausnahme ausgelöst wurde ---
   bei System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   bei System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   bei Microsoft.PowerBI.Client.Windows.Modeling.Hosting.ModelingTelemetryService.<>c__DisplayClass8_0`1.<<RunInAsyncActivity>b__0>d.MoveNext()
--- Ende der Stapelüberwachung vom vorhergehenden Ort, an dem die Ausnahme ausgelöst wurde ---
   bei System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   bei System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   bei Microsoft.PowerBI.Client.Windows.Modeling.Hosting.ModelingTelemetryService.<RunInAsyncActivity>d__7.MoveNext()
--- Ende der Stapelüberwachung vom vorhergehenden Ort, an dem die Ausnahme ausgelöst wurde ---
   bei System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   bei System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   bei Microsoft.PowerBI.Client.Windows.Modeling.Hosting.ModelingTelemetryService.<RunInAsyncActivity>d__8`1.MoveNext()
--- Ende der Stapelüberwachung vom vorhergehenden Ort, an dem die Ausnahme ausgelöst wurde ---
   bei System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   bei System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   bei Microsoft.PowerBI.Modeling.Engine.Loader.LoadToReportFlow.<PerformLoadToAnalysisServices>d__18.MoveNext()
--- Ende der Stapelüberwachung vom vorhergehenden Ort, an dem die Ausnahme ausgelöst wurde ---
   bei System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   bei ......

[JoachimSA]

Okay, forget my last reply. This is only the case when I join several files of course. 
The Sharpoint file connector is a litrtle more convoluted in used because you always seem to have to start at the root of the sharpoint folder. In my case this is a MS Teams Folder which has pretty deep hierarchies because manys people work on it. The trick seems to be to filter the filename(s) you need. Nice sideeffect: id does not matter when you move the file as the search will always find it.

[Anonymous]

Hi @JoachimSA ,

Can you provide a screenshot of the error message? 

What I'd suggest is:

1. Clear the Power BI cache, File -> Options and Settings -> Options -> (GLOBAL) Data Load -> click on "Clear Cache".

2. Re create the report from scratch.

There is also to ensure that Power BI Desktop is the latest version.

For creating relationships, there is an automatic relationship creation function that you can try to use, which may reduce the difficulty of your work. Reference: Create and manage relationships in Power BI Desktop - Power BI | Microsoft Docs

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JoachimSA]

Hi Stephen,

I tried the cache clearing without success.

The error window is in German:

Also: it seems to help to save the file without applying the Power Query changes and then opening the PBIX again...strange

[Anonymous]

Hi @JoachimSA ,

Sorry I don't know the language in your screenshots, maybe you should try to open a support ticket for help.

Go to https://powerbi.microsoft.com/en-us/support/ ,scroll down and click "CREATE SUPPORT TICKET" for further help if you are a pro user.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JoachimSA]

The German error says "the sequence does not contain data".
I now moved away from the DQ approach. Instead I export a "condensed" version of my result as Excel FIle using DAX Studio. For this I use multiple EVALUATE statements with SUMMARIZECOLUMNS for the needed fact and dimension tables (always including the primary key). This seems to work a lot more stable. moreover, I can version my exports and even build a little tool to compare the within power BI 😉

[KNP]

I'm not sure about the cause of the error but I'd recommend using the SharePoint.Files() connector.

I think it will perform at least as well as and may not suffer the same issues.

https://docs.microsoft.com/en-us/powerquery-m/sharepoint-files

[JoachimSA]

Hi KNP,

Thanks for the suggestion.

I tried it with the Sharpoint.files connector - did not work. Also the import requires this convoluted proces with extracting sample data etc (see picture -sorry for the German, maybe you get the point anyways ;-))

What does help is saving without applying the Power Query chances and then closing and reloading the PBIX file....looks buggy to me...