Power BI file slow / crashing

[AVIE]

Hi,

I have a Power BI file of a very small size (159 kB). 

It is built up via PowerQuery by loading +-45 different Excel files. All of these Excel files are also extremely small (+- 200 kB per file). I have created an additional table in PowerQuery combining all of these 45 different Excel files into 1. Thus, I have disabled the load of the separate Excel files so that only the combined table will load to Power BI Desktop. This final combined table consists of 10 columns and 5.000 rows. I have added 5 calculated columns and 5 measures and a date table.

Despite of the small size of my file, it is extremely slow when changing tabs, when taking actions, etc. Also, it freezes 75% of the time shortly after startup.

My computer has 16GB RAM, 64bit Windows, 64 bit version of Power BI so this shouldn't be the reason. I have downloaded the latest version of Power BI, have already tried to uninstall and re-install.

My source tables are loading from the server at work via VPN (Cisco Anyconnect). Could this be the cause? And how can I investigate whether that's the cause? I can't save these files locally on my computer as they're company property and should be accessible via the company's server. Unfortunately, I can't go to the office either to test if it's working better locally as it's forbidden due to COVID. Basically, the file really needs to work via VPN as well.

Please let me know how I can get this performance fixed!

Thanks!

Below I'm adding the latest message that I was able to copy when Power Bi crashed:

Feedback Type:

Frown (Error)

Error Message:

We’re sorry. A problem occurred while rendering the report, and Power BI Desktop will close now. Please restart.

Stack Trace:

Microsoft.PowerBI.Client.Windows.CefSharpFramework.Exceptions.CefProcessCrashedException

   at Microsoft.PowerBI.Client.Windows.CefSharpFramework.CefWebBrowser.<>c__DisplayClass55_1.<OnRenderProcessTerminated>b__1()

Stack Trace Message:

We’re sorry. A problem occurred while rendering the report, and Power BI Desktop will close now. Please restart.

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__DisplayClass15_0.<HandleException>b__0()

   at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)

   at Microsoft.PowerBI.Client.Windows.Utilities.UIBlockingAwareExceptionHandler.HandleException(Exception e)

   at Microsoft.PowerBI.Client.Windows.MainWindow.Microsoft.PowerBI.Client.Windows.CefSharpFramework.ICefBrowserHost.OnFatalError(FeedbackActionInfo feedbackActionInfo, Exception e, String knownErrorDetailsUrl)

   at Microsoft.PowerBI.Client.Windows.CefSharpFramework.CefWebBrowser.<>c__DisplayClass55_1.<OnRenderProcessTerminated>b__1()

   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.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)

   at Microsoft.PowerBI.Client.Program.<>c__DisplayClass4_0.<Main>b__1()

   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_ErrorDescription:

ProcessCrashed

PowerBINonFatalError:

{"AppName":"PBIDesktop","AppVersion":"2.90.1081.0","ModuleName":"Microsoft.PowerBI.Client.Windows.dll","Component":"Microsoft.PowerBI.Client.Windows.CefSharpFramework.CefWebBrowser+<>c__DisplayClass55_1","Error":"Microsoft.PowerBI.Client.Windows.CefSharpFramework.Exceptions.CefProcessCrashedException - ProcessCrashed","MethodDef":"<OnRenderProcessTerminated>b__1","ErrorOffset":"21"}

Snapshot Trace Logs:

C:\Users\503115\Microsoft\Power BI Desktop Store App\FrownSnapShot0f093112-78c5-47a3-ae0d-89539f2aadd9.zip

Model Default Mode:

Import

Model Version:

PowerBI_V3

Performance Trace Logs:

C:\Users\503115\Microsoft\Power BI Desktop Store App\PerformanceTraces.zip

Enabled Preview Features:

PBI_NewWebTableInference

PBI_eimInformationProtectionForDesktop

Disabled Preview Features:

PBI_shapeMapVisualEnabled

PBI_SpanishLinguisticsEnabled

PBI_JsonTableInference

PBI_ImportTextByExample

PBI_ExcelTableInference

PBI_qnaLiveConnect

PBI_azureMapVisual

PBI_dataPointLassoSelect

PBI_compositeModelsOverAS

PBI_narrativeTextBox

PBI_dynamicParameters

PBI_anomalyDetection

PBI_newFieldList

PBI_cartesianMultiplesAuthoring

Disabled DirectQuery Options:

TreatHanaAsRelationalSource

Cloud:

GlobalCloud

Browser name:

DataExploreView

PowerBIUserFeedbackServices_IsReported:

True

[v-shex-msft]

HI @AVIE,

How did you merge these query tables? Can you please explain more about these? 

How to Get Your Question Answered Quickly

BTW, have you used multiple iterator functions in your expressions? They may also affect the performance.

Optimizing nested iterators in DAX 
Regards,

Xiaoxin Sheng

[AVIE]

Hi,

My own solution I mentioned below resolved the problem so no need to look into this anymore!

Thank you!

[lbendlin]

you may also want to test to disable parallel loading of data sources, otherwise you risk overwhelming the file server.

[lbendlin]

Does it crash when you remove all measures?

Are you saying that you use 45 Power Query queries and then combining them into one report query?  If so, please consider not doing that, but instead use the Folder connector and let Power Query combine the files for you (assuming they all have the same structure and you collect the data from the same place in each file)

Are your Excel files .xlsb or .xlsx ?

[AVIE · Accepted solution]

Dear @lbendlin ,

Thank you for your response! 

The sources are unstructured (I need to adapt them before being appendable) and all come from a different folder, so I don't think your solution will work? Or would it? They are all xlsx files.

The issue does not disappear when removing the measures.

I have unchecked the settings box to allow data previews to download in the background and it seems like it's running normally now... Not 100% sure yet whether it won't crash anymore, but it seems to work way better at least! So possibly this might have been the solution.