Report not refreshing - Error Message: Sequence contains no matching element

[DemoFour]

Afternoon,

I have an issue with amending a source model, the report is working OK in the service and is refreshed and up to date. I need to make a change to some business logic, but when I refresh the report it will not load due to the Error Message:
Sequence contains no matching element - I have checked the table and there is nothing going on in it in respects to changing names of fields and types. If I remove the table from the model, the report will refresh!? 

Below is the text I get from the Error.  I have done a forum search but the solutions are several years out of date, so I am a bit stumped as they downgraded to a previous PBI file but this did not always work. 

Any help please 🙂

Feedback Type:
Frown (Error)

Error Message:
Sequence contains no matching element

Stack Trace:
System.InvalidOperationException
at System.Linq.Enumerable.Single[TSource](IEnumerable`1 source, Func`2 predicate)
at Microsoft.PowerBI.Client.Windows.AnalysisServices.ModelingTableResult.CreateColumns(Query query, TableResult tableResult, TableSchemaResult schemaTableResult, Boolean isDirectQuery, CaptionsBehavior captionsBehavior)
at Microsoft.PowerBI.Client.Windows.AnalysisServices.QueryEvaluator.LoadToModelQueryEvaluator.<EvaluateQuery>d__9.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.Client.Windows.AnalysisServices.QueryEvaluator.EvaluationUtilities.<CaptureOutputOrException>d__0.MoveNext()

Stack Trace Message:
Sequence contains no matching element

Invocation Stack Trace:
at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
at Microsoft.Mashup.Client.UI.Shared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace, String exceptionMessage)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.GetStackTraceInfo(Exception e)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass14_0.<HandleException>b__0()
at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>c__DisplayClass2_1.<SendAndMarshalExceptions>b__0(Object null)
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Delegate.DynamicInvokeImpl(Object[] args)
at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
at Microsoft.Mashup.Client.UI.Windows.DialogWindowBase.ShowModal(IWindowHandle windowHandle)
at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
at Microsoft.PowerBI.Client.Windows.FloatingDialog.KoLoadToReportDialog.TryShowDialog(Report report, IExceptionHandler exceptionHandler, IPowerBIWindowService windowService, IPowerBIQueryUIService queryServices, IDesktopModelingHost modelingHost, LocalizedString title, LoadToModelContext loadToModelContext)
at Microsoft.PowerBI.Client.Windows.FloatingDialog.KoLoadToReportDialog.<>c__DisplayClass16_0.<TryShowDialogForQueries>b__0()
at Microsoft.PowerBI.Client.Windows.Report.<>c__DisplayClass64_0`1.<SynchronizeLooselyWithQueries>b__0()
at Microsoft.PowerBI.Client.Windows.Report.<SynchronizeLooselyWithQueriesAsync>d__65`1.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start[TStateMachine](TStateMachine& stateMachine)
at Microsoft.PowerBI.Client.Windows.Report.SynchronizeLooselyWithQueriesAsync[T](Func`1 getTask)
at Microsoft.PowerBI.Client.Windows.Report.SynchronizeLooselyWithQueries[T](Func`1 action)
at Microsoft.PowerBI.Client.Windows.Commands.ApplicationCommands.DataImportCommands.RefreshAllQueries(IPowerBIWindowService windowService, IExceptionHandler exceptionHandler, RefreshAllQueriesParameters parameters)
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at Microsoft.Practices.Prism.Commands.DelegateCommandBase.<>c__DisplayClass4_0.<.ctor>b__0(Object arg)
at Microsoft.PowerBI.Client.Windows.Commands.CompositeCommand`1.Execute(ICommand command, Object parameter)
at Microsoft.Practices.Prism.Commands.CompositeCommand.Execute(Object parameter)
at Microsoft.PowerBI.Client.Windows.Commands.CompositeCommand`1.Execute(T parameter)
at Microsoft.PowerBI.Client.Windows.CommandStoreHostService.<ExecuteCore>d__14.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
at Microsoft.PowerBI.Client.Windows.CommandStoreHostService.ExecuteCore(String commandId, String parameterJson, Int64 promiseHandle)
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<HandleAwaitableAsyncExceptions>d__1.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.HandleAwaitableAsyncExceptions(IExceptionHandler exceptionHandler, Func`1 asyncFunc)
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<HandleAsyncExceptions>d__0.MoveNext()
at System.Runtime.CompilerServices.AsyncVoidMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.HandleAsyncExceptions(IExceptionHandler exceptionHandler, Func`1 asyncFunc)
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Delegate.DynamicInvokeImpl(Object[] args)
at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
at Microsoft.PowerBI.Client.Program.<>c__DisplayClass4_1.<Main>b__2()
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<>c__DisplayClass3_0.<HandleExceptionsWithNestedTasks>b__0()
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at Microsoft.PowerBI.Client.Program.Main(String[] args)

PowerBINonFatalError:
{"AppName":"PBIDesktop","AppVersion":"2.107.841.0","ModuleName":"Microsoft.PowerBI.Client.Windows.dll","Component":"Microsoft.PowerBI.Client.Windows.AnalysisServices.ModelingTableResult","Error":"System.InvalidOperationException","MethodDef":"CreateColumns","ErrorOffset":"550"}

Snapshot Trace Logs:
C:\Users\RussellSlater\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShot8babd54d-601e-484b-b6c3-fa8ea30a1e9f.zip

Model Default Mode:
Import

Model Version:
PowerBI_V3

Performance Trace Logs:
C:\Users\RussellSlater\AppData\Local\Microsoft\Power BI Desktop\PerformanceTraces.zip

Enabled Preview Features:
PBI_compositeModelsOverAS
PBI_enableWebView2
PBI_mobileAuthoringFormattingUI
PBI_sparklines
PBI_scorecardVisual

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_qnaLiveConnect
PBI_azureMapVisual
PBI_enhancedTooltips
PQ_WebView2Connector
PBI_fieldParametersSuperSwitch

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

PowerBIUserFeedbackServices_IsReported:
True

[farrahi1]

Hi! I'm experiencing the same problem when refreshing reports. 

[DemoFour]

I have also checked back up versions and earlier dev versions and they all produce this error. 

I am stumped as the forum mentions getting this error when tables have been merged or appended, but this is minimal transformations in the query. 

[v-yingjl]

Similar issue has been submited(ICM: 325094959), would update here as soon as possible if there is any progress about it.

Best Regards,
Community Support Team _ Yingjie Li

[DemoFour]

@v-yingjl Thank you for the update, much appreciated. 

[datageekau]

the same issue started immediately after updating to desktop version 2.108.825.0
Tried to roll back and the issue remained. And tried both 32-bit and 64-bit desktop app. 

Have refreshed every table in Power Query and Power BI individually with no errors. So can not replicate for any single table.

Only happens when you try to refresh all data in Power BI.

I am using SharePoint and dataverse connections. Works in the Power BI service. Download the file, open on the desktop and try to refresh and you get the "Sequence contains no matching element" for all tables.

looks like something in 2.108.825.0 is causing it. 

[DemoFour]

Good afternoon  @v-yingjl , 

Is there any resolve to this issue, I have just rebuilt a new report and was checking tables loading, the first two attempts worked, but then subsequent work and bringing data into desktop resulted in the same issue happening again! 

Thanks 

[datageekau]

I haven't found the cause. I was able to discover the tables causing the issue for me. 

1. Make a copy of your report file (and a back just in case) to do the work to find the table.

2. Edit the Queries of the tables. Then in Power Query for the tables you suspect may have the issue 'right click' the query and unselect 'Enable Load'
This will stop that query from loading.

NOTE this will remove all measures on the Associated table and this is why you want to do this on a copy of your report first to determine which table(s)/querie(s) are the issue?

Close and Apply, Then in Power BI refresh and see if you have the error. 
You will repeat this process turning tables on and off until you find which tables have the issue. 
For me, I turned off half of my tables. The error went away. then I turned half of them back by selecting "Enable Load" and did this several times until I was able to isolate the issue to two tables where with them on they caused the error and with them off the error didn't happen.

Once you have determined the tables you can go back to your actual file. Or make a copy again and check your work. If the tables with issues have measures. Make sure you move/recreate the measures on a different table like their own measures table and then you hopefully will be able to delete the table and re-add it fresh.

In my case, it was weird as the two tables causing the issue were a reference to another table as they were being bought in as dimensions for filtering. The original table was imported with no issue, just the referenced table had the issue. I ended up just duplicating the tables instead of referencing them as they were so small. . Only reason I had used reference was to save additional calls to the server for exactly the same query.

Hope this helps you at least locate the issue table if you run into this error. when you have 55+ tables and they all have the same error because of one table it's a bit daunting where to look. 

[DemoFour]

Morning @datageekau thank you for posting up a work around to the thread. 

This is good advice, I did exactly the same thing to find the table creating the error.

However, when I brought the "problem" table in first it loaded OK, then all the other tables all loaded in one at a time, made sure all was working. However, when I did more work and brought new tables in the error returned! and I had to start all over again, but this time I knew the problem table. 

I did spend several hours with the Microsoft support team, and this is a bug that has been passed on to the product team for a fix. 

I fixed the issue by reverting back to June PBI version, but this has meant other issues due to reports I have built since June, giving me the "this file is built in a newer version and may not be compatible" error. 

The feedback that I received is that hopefully this bug will be fixed for the next version of PBI desktop. 

[kstiegler]

I am encountering this issue in the September 2022, August 2022, and July 2022 Power BI Desktop updates. Only in the June 2022 version can my reports be successfully refreshed from the desktop. The published reports refresh successfully in the service, and the data previews can be refreshed successfully in the Power Query Editor; however, manually refreshing the data in the main Power BI Desktop UI (not Power Query) fails.

[P3Sven]

Hi @v-yingjl ,

we have the same issue since July 2022, although we haven't changed something in DataSet..

Any news about resolve status?

BR,

Sven