One of the great things about the SQLBits conference in the UK is the way that all the session recordings are made available for for anyone to view for free on their YouTube channel. All the recordings from the 2024 conference have now been posted and it’s a goldmine for anyone who wants to deepen their Power BI or Fabric knowledge. Here are the recordings for my three sessions: Fabric Direct Lake Deep Dive What’s new in Power Query in Power BI, Fabric and Excel? Connect Excel to Power BI data with Live Connected Tables There are so many other great sessions that it’s hard to pick out any h ..read more

Concluding my series of blog posts on seemingly harmless things you can do in a Power BI report that can lead to performance problems and the “This visual has exceeded the available resources” error (see also “Calculate(), Filter() and DAX memory usage” and “DAX measures that never return blank“), in this post I’ll show how table visuals with vertical scrollbars that potentially show thousands of rows can be a Bad Thing. Using the Import mode semantic model I’ve used in the last two posts, consider this table visual: The two measures displayed are trivial counts and averages. However there ar ..read more

Following on from my earlier post on the Query Memory Limit in Power BI, and as companion to last week’s post on how a DAX antipattern using Calculate() and Filter() can lead to excessive memory consumption by queries (and therefore lead to you hitting the Query Memory Limit), in this post I want to look at the effects of another DAX antipattern on performance and memory usage: measures that can never return a blank value. Adding zero to the result returned by a measure What do I mean by a measure that never returns blank? Well using the same model that I used in last week’s post, let’s say I ..read more

Following on from my last post on the Query Memory Limit in Power BI, in this post I want to look at one of the most common DAX antipatterns and its effect on query memory usage: filtering on a whole table, rather than an individual column, in the filter parameters of the Calculate() function. A lot has already been written on this particular antipattern from the point of view of query performance – for example see here and here – but it’s only with the recent addition of the Execution Metrics Profiler/Log Analytics event that you can see how bad it is for memory usage too. Here’s a simple exa ..read more

Continuing my series on Power BI memory errors (see part 1, part 2 and part 3), in this post I’ll look at the query memory limit which controls the amount of memory that an individual query can consume. This is a subject which I’ve talked about in a few blog posts in the past: hitting this limit is one of the reasons you’ll see the “This visual has exceeded the available resources” error which I blogged about here and and here. There’s also some official documentation here which is pretty good. As I mentioned in part 1 of this series, when you run a DAX query against a Power BI semantic model ..read more

When you’re using Excel workbooks as a data source in Power Query in either Excel or Power BI you may encounter the following error when trying to connect: DataFormat.Error: File contains corrupted data. There are three possible causes of this error that I know of, two of which are well documented. First, the file may actually be corrupt in which case I hope you have a backup! Second, the Excel file may be password protected. Power Query cannot connect to a password protected Excel file so you’ll need to remove the password protection before you can use it as a source. The third is one I ran ..read more

Everyone is excited about Power BI Copilot, and the newly-announced preview of being able to use Copilot to ask questions about all the data in your semantic model rather than just what is shown in a report is a massive step forward. However amazing the LLMs used behind the scenes are, though, the quality of the results your users get from this new Copilot preview depends on a number of factors that you as a developer control. How you model your data is very important and as the announcement blog mentions, so is the linguistic schema that was originally added for Copilot’s predecessor feature ..read more

Continuing my series on Power BI model memory errors (see part 1 and part 2), in this post I will look at the Command Memory Limit which restricts the amount of memory that XMLA commands like Create, Alter and most importantly Refresh and can use. If you’ve ever been told that your semantic model should consume less than half the amount of memory available to it because memory consumption can double during a full refresh, then that is because of the Command Memory Limit. Every time a model is refreshed in Power BI, that refresh is initiated by running a Refresh command. During the refresh a co ..read more

The ever-vigilant folks on the internet have spotted that there’s a new M function in the latest versions of Power BI and Excel: Module.Versions. This function, at the time of writing, returns a record with a single field in that contains the version number of the Power Query engine currently in use. So for example if I have a Power Query query in Power BI Desktop that consists of the following code: Module.Versions() It returns the following: …where 2.129.181.0 is the version of the Power Query engine in my build of Power BI Desktop. This function was introduced for people developing Power ..read more

Since the announcement in March that Power BI Premium P-SKUs are being retired and that customers will need to migrate to F-SKU capacities intead I have been asked the same question several times: Why are you forcing me to migrate to Fabric??? This thread on Reddit is a great example. What I want to make clear in this post is the following: Moving from P-SKU capacities to F-SKU capacities is not the same thing as enabling Fabric in your tenant No-one is being forced to migrate from Power BI to Fabric and using F-SKU capacities does not mean you are using Fabric. Access to Fabric for your users ..read more