Errors when trying to Refresh and/or Apply changes in Power Query

[Ludzik1993]

Hi all!

I do have a problem with one of ours Power BI Reports in Desktop (all works perferctly fine when on Power BI Service)

The data source for the report is Dataverse and AAD (basically Power Apps application & information's about users).

There is one main table, and 6 others where 3 serves like straight lookups, and 3 others as sources for some Canvas apps fields which are multiple choice, and they way they are set up is that are storing values in string fields using delimiters.

The other thing is that some of them are Dual and some are Direct connection (I was told it's because the they were not able to use pipelines in Power BI Service with Import queries.

The issue is with queries evaluation and in general making any changes to the semantic model (just to note: everything is working fine in Power BI Service), that when I want to make even the smallest change (currently I'm modifying main table conditional column to include one more condition) - that the applying the changes simply don't work. Also making any (even the mentioned, or deleting one step from the end of query) is like 2-3 minutes of loading.

There is always some sort of error:

• Stuck on Evaluating (rareley)

• Stuck on Establishing Direct Query Connection (rareley)
• Stuck on Detecting Table Scheme (rareley)

• Stuck on waiting for other queries (this is also common - the step within mentioned 'Added Custom4' is just creating a string from row ID (not GUID) and Row Name -> for searching purpose), and when I clicl Close, and Apply Changes again, the number of these error will decreas and at the end WebViewe2 will through an error.

• WebViwer2 error (this one is crushing Power BI - and is the most common) - also sometimes the loading is successful, like I had a minute ago (green checkmarks and No. of rows loaded) -> but still there is this WebView2 error that's at the end crushing Power BI 😞 - so no use of that.

Always something... Currently I'm trying to apply these changes for a 2nd day straight. And these error appears after like 10 minutes of Power BI trying to do them.

Also when evaluating the processor goes just crazy.

Alsso all what I'm doing have to be done on Virtual Machine.

Does anyone have any idea what that might be? Or how to fix it?

The datasources are not big -> the main table is now around 1200 rows, and the others are like 6-20 rows.

[christinepayton]

When you work with Dataverse sources, you absolutely need to select the columns you want in PQ and then right click -> remove other columns. Some of the tables have 600+ columns on them, and if you try to import them all it will not be able to refresh in any kind of timely fashion. This query step folds, so if you do it first in your steps you'll be pulling less data-- 

[rajendraongole1]

Hi @Ludzik1993 - Since you’re using Direct Query and Import modes with Dataverse, make sure your transformations are compatible with query folding wherever possible. Complex steps like custom columns or row concatenations might prevent folding, slowing down query evaluation significantly.

Minimize the steps in Power Query, especially if they involve string manipulation on large datasets. Try combining multiple transformations into fewer steps if possible.

Ensure that your VM has adequate CPU and memory. Power BI can be resource-intensive, especially with mixed Direct Query and Import data models.

Running Power BI Desktop on a VM with limited resources can lead to performance issues if other applications are consuming memory or CPU. Close any non-essential applications to free up resources.

Since DirectQuery mode is often more resource-intensive, you may want to consider simplifying your model. Check if some of the tables currently in DirectQuery mode could instead be imported if they aren’t frequently updated.

Optimize Dataverse Queries: If you have access to Dataverse, consider optimizing the database side (e.g., simplifying views, adding indexes) to make the DirectQuery mode more efficient.

since Power BI Desktop WebView2 instability and DirectQuery performance are both areas where updates and patches are regularly released. Each update could potentially address issues specific to VM environments or mixed storage modes.

Solved: Problem with webview2: out of memory error - Microsoft Fabric Community

Solved: Webview2 Process Failed. Out of Memory? - Microsoft Fabric Community

[Ludzik1993]

Thanks a lot! - I'll look at all of this ❤️