Chris Webb BI

MARCH 10, 2024

Many Power BI connectors for relational databases, such as the SQL Server connector, have an advanced option to control whether relationship columns are returned or not. By default this option is on. Returning these relationship columns adds a small overhead to the time taken to open a connection to a data source and so, for Power BI DirectQuery semantic models, turning this option off can improve report performance slightly.

Profiling 234

Profiling Information IT 234

Chris Webb BI

MARCH 3, 2024

If you’re tuning a DirectQuery semantic model in Power BI one of the most important things you need to measure is the total amount of time spent querying your data source(s). Now that the queries Power BI generates to get data from your source can be run in parallel it means you can’t just sum up the durations of the individual queries sent to get the end-to-end duration.

Profiling 281

Profiling IT 281

Chris Webb BI

FEBRUARY 25, 2024

Hybrid tables – tables which contain both Import mode and DirectQuery mode partitions to hold data from different time periods – have been around for a while. They are useful in cases where your historic data doesn’t change but your most recent data changes very frequently and you need to reflect those changes in your reports; you can also have “ reverse hybrid tables ” where the latest data is in Import mode but your historic data (which may not be queried often bu

IT 166

IT 166

Chris Webb BI

FEBRUARY 18, 2024

The more you monitor something the more likely it is that you’ll see something strange you can’t explain. This is true of several customers I’ve spoken to recently who saw DAX queries run in the middle of the night by people who were definitely not online at the time and who were worried about the resulting load on their Premium capacities.

Profiling 262

Profiling Sales IT 262

Chris Webb BI

FEBRUARY 11, 2024

There’s a new M function in Power Query in Power BI that allows you to read the data from a Parquet file: Parquet.Metadata. It’s not documented yet and it’s currently marked as “intended for internal use only” but I’ve been told I can blog about it. Here’s an example of how to use it: let Source = Parquet.Metadata(File.Contents("C:myfile.snappy.parquet")) in Source …and here’s an example of the output: This query shows how to expand the recor

Information 237

Information IT 237

Chris Webb BI

FEBRUARY 4, 2024

I don’t have anything against the use of calculated columns in Power BI semantic models in general but you do need to be careful using them with DirectQuery mode. In particular when you have a DirectQuery connection to another Power BI semantic model – also known as a composite model on a Power BI semantic model – it’s very easy to cause serious performance problems with calculated columns.

Sales 263

Sales IT 263

Chris Webb BI

JANUARY 30, 2024

One of the most popular questions on the forums I follow is “How can I learn Power BI and Fabric?” There are a ton of great, free online resources out there – blogs, videos and so on – but the people answering this question often do not mention user groups and conferences. I think that’s a mistake because, at least for me, attending (and indeed speaking at) online and in-person events is one of the most effective ways of learning.

Learning 157

Learning Travel Globalization IT 157

Chris Webb BI

JANUARY 28, 2024

If you’re writing your own M code in Power Query in Power BI or Excel you may run into the following error: Expression.Error: Evaluation resulted in a stack overflow and cannot continue. If you’re a programmer you probably know what a stack overflow is; if you’re not you might search for the term, find this Wikipedia article and still have no clue what has happened.

IT 163

IT 163

Chris Webb BI

JANUARY 21, 2024

One of the most important properties you can set in a Power BI DirectQuery semantic model is the “Maximum connections per data source” property, which controls the number of connections that can be used to run queries against a data source. The good news is that the maximum value that you can set this property to has just been increased in Premium.

IT 229

IT 229

Chris Webb BI

JANUARY 18, 2024

In my last post but one I showed how you could create a DGML file that contains detailed information on what happens during a Power BI Import mode refresh – the “job graph” – and visualise that in Visual Studio or VS Code, to help with performance tuning. In my last post , I explained the concepts of blocking and waiting which are key to understanding this data.

Analysis 192

Analysis Information IT 192

Chris Webb BI

JANUARY 14, 2024

Following on from my previous post showing how you can visualise the job graph for a Power BI Import mode semantic model refresh, I this post I will look at how you can interpret what the job graph tells you – specifically, explaining the concepts of blocking and waiting. As always, simple examples are the best way of doing this. Blocking Blocking occurs when one job can only start when one or more other jobs have completed because there is a dependency between them.

IT 243

IT 243

Chris Webb BI

JANUARY 7, 2024

A few years ago a new pair of Profiler events was added for Power BI Import mode datasets (and indeed AAS models): the Job Graph events. I blogged about them here but they never got used by anyone because it was extremely difficult to extract useful data from them – you had to run a Profiler trace, save the trace file, run a Python script to generate a.dgml file, then open that file in Visual Studio – which was a shame because they contain a lot of really interesting, useful informat

Profiling 276

Profiling Information IT 276

Chris Webb BI

DECEMBER 30, 2023

Every year, on the anniversary of the first-ever post on this blog, I write a post reflecting on what has happened to me professionally in the past year and this year the subject is obvious: Fabric. Of course I knew about, and indeed had been playing with, Fabric for a long time before even the private previews but it was only with the public preview and then GA that I felt the real impact on my job.

Analysis 144

Analysis Knowledge Learning Business 144

Chris Webb BI

DECEMBER 23, 2023

A year ago support for nested data types in Excel was announced on the Excel blog , but the announcement didn’t have much detail about what nested data types are and the docs are quite vague too. I was recently asked how to create a nested data type and while it turns out to be quite easy, I thought it would be good to write a short post showing how to do it.

IT 213

IT 213

Chris Webb BI

DECEMBER 21, 2023

I don’t know how, but somehow I missed the announcement of the preview of Project Sophia at Ignite (it’s not built by the Fabric product group and I’ve been distracted by all the cool stuff we’re working on at the moment, so…). If you’re a fan of Power BI and data in general you are definitely going to want to check it out though!

Research 221

Research Business Information IT 221

Chris Webb BI

DECEMBER 17, 2023

One of the coolest features in Fabric is Direct Lake mode, which allows you to build Power BI reports directly on top of Delta tables in your data lake without having to wait for a semantic model to refresh. However not everyone is ready for Fabric yet so there’s also a lot of interest in the new DeltaLake.Table M function which allows Power Query (in semantic models or dataflows) to read data from Delta tables.

IT 277

IT 277

Chris Webb BI

DECEMBER 10, 2023

Something I mentioned in my recent post about the new DeltaLake.Tables M function on the Fabric blog recently was the fact that you can get different versions of the data held in a Delta table using the Value.Versions M function. In fact, the Value.Versions is the way to access different versions of data in any source that has this concept – so long as Power Query has added support for doing so.

IT 233

IT 233

Chris Webb BI

DECEMBER 3, 2023

Power Query allows you to merge (“join” in database terms) two tables together in a variety of different ways, including left and right anti joins. Unfortunately, as I found recently, anti joins don’t fold on SQL Server-related data sources, which can result in performance problems. Luckily there is a different way of doing anti joins that does fold.

IT 226

IT 226

Chris Webb BI

NOVEMBER 26, 2023

A lot of DAX performance problems relate to the use of strict and eager evaluation with the IF or SWITCH functions. It’s an incredibly complex, “it depends”, black-box type of topic and naturally Marco and Alberto have a great article on it here. Rather than describe any general rules – which would be pretty much impossible – in this blog post I want to show a specific scenario that illustrates how the use of DAX variables can influence whether strict or eager evalu

Information 250

Information IT 250

Chris Webb BI

NOVEMBER 18, 2023

Last week it was announced that Power BI datasets have been renamed: they are now semantic models. You can read the announcement blog post here and see the change in the Fabric/Power BI UI already. The name change proved to be surprisingly uncontroversial. Of course it’s very disruptive – trust me, I know, I have around 500 blog posts that I need to do a search-and-replace on at some point – so I have a lot of sympathy for people with books or training courses that need updatin

Business 280

Business IT 280

Chris Webb BI

NOVEMBER 12, 2023

Excel workbooks are one of the slowest data sources you can use with Power Query in Excel or Power BI. Reading small amounts of data from small workbooks is usually fast; reading large amounts of data from large workbooks can be very slow. But what about reading small amounts of data from large Excel workbooks? I did some tests and it turns out that performance can vary a lot depending on where your data is in the workbook and how that workbook is structured.

Sales 237

Sales Learning IT 237

Chris Webb BI

NOVEMBER 5, 2023

Recently I was involved in an interesting discussion on Twitter X about how partitioning a table in a Power BI dataset semantic model can affect compression and therefore its size and query performance. You can read the thread here. It got me thinking: is there a way to get more detail on how well compression, and in particular run-length encoding (RLE from hereon), is working for a column when you’re using Import mode or Direct Lake?

Analysis 220

Analysis Research Information Learning 220

Chris Webb BI

OCTOBER 29, 2023

If you’re a fan of using Log Analytics for monitoring Power BI activity , then you may have noticed there’s some new data in the ApplicationContext column of the PowerBIDatasetsWorkspace table. Up until recently the ApplicationContext column only contained IDs that identify the report and the visual that generated a DAX query (something I blogged about here ); it now contains additional information on the type of Power BI report that generated the query and an ID for the user session

Information 215

Information IT 215

Chris Webb BI

OCTOBER 22, 2023

Back in 2021 I wrote a post showing how you can link a DAX query generated by a Power BI report in Log Analytics to a visual in a Power BI report. In that post I showed how you can get the unique IDs for each visual in a report using Performance Analyzer, an approach which works but has drawbacks. Then, in 2022 I wrote another post showing how you can get the same IDs using the Power BI Embedded Analytics Playground, which again works but isn’t perfect.

IT 206

IT 206

Chris Webb BI

OCTOBER 15, 2023

In the Power BI/Fabric community everyone is excited about the recent release of Semantic Link : the ability to analyse Power BI data easily using Python in Fabric notebooks. Sandeep Pawar has an excellent blog post here explaining what this is and why it’s so cool. Meanwhile in the Excel community, everyone is excited about the new integration of Python into Excel.

Sales 262

Sales Analysis Learning IT 262

Chris Webb BI

OCTOBER 8, 2023

If you’ve heard about the new Direct Lake mode for Power BI datasets in Fabric you’ll know that it gives you the query performance of Import mode (well, almost) without the need to actually import any data. Direct Lake datasets can be refreshed though – in fact, they refresh automatically by default – and if you look at the dataset’s Refresh History you’ll see there’s a Direct Lake section which sometimes shows errors: Also, if you look at a custom datas

IT 194

IT Profiling Travel Sales 194

Chris Webb BI

OCTOBER 1, 2023

This week, one of my colleagues at Microsoft, Nat Van Gulck, showed me a cool new open-source tool he’s been working on to make VisOps for Power BI much easier: PBI Inspector. What is VisOps? I’ll admit I didn’t really know either, so being lazy I asked Nat to write a few paragraphs describing the project and why it will be useful: Great progress has been made over the years with software development CICD tooling and processes (aka DevOps), just not so much with BI report visua

Innovation 191

Innovation IT 191

Chris Webb BI

SEPTEMBER 24, 2023

One of the most popular posts on my blog in the last few years has been this one: Keep The Existing Data In Your Power BI Dataset And Add New Data To It Using Incremental Refresh To be honest I’m slightly ashamed of this fact because, as I say in the post, the solution I describe is a bit of a hack – but at the same time, the post is popular because a lot of people have the problem of needing to add new data to the data that’s already there in their Power BI dataset and there&#

IT 267

IT Learning Management 267

Chris Webb BI

SEPTEMBER 17, 2023

A few weeks ago an important new feature for managing connections to data sources in the Power BI Service was released: Shareable Cloud Connections. You can read the blog post announcing them here. I won’t describe their functionality because the post already does that perfectly well; I want to focus on one thing in particular that is important for anyone using Power BI with Snowflake (and, I believe BigQuery and probably several other non-Microsoft sources): Shareable Cloud Connections al

Management 243

Management IT 243

Chris Webb BI

SEPTEMBER 10, 2023

While I was at the Data Scotland conference in Edinburgh on Friday (great event by the way) I stopped by the Tabular Editor stand and got the nice people there to give me a demo of their new tool, DAX Optimizer. It’s currently in private beta but if you’re curious to learn more, Nikola Ilic has already blogged about it in detail here. Rather than blog about the tool itself – there’s no point repeating Nikola’s post – I thought it would be good to answer a ques

Innovation 246

Innovation Learning Business IT 246

Chris Webb BI

SEPTEMBER 3, 2023

If you read this post that was published on the Fabric blog back in July, you’ll know that each Power Query query in a Fabric Gen2 dataflow has a property that determines whether its output is staged or not – where “staged” means that the output is written to the (soon-to-be hidden) Lakehouse linked to the dataflow, regardless of whether you have set a destination for the query output to be written to.

IT 277

IT 277

Chris Webb BI

AUGUST 27, 2023

Sometimes when you’re importing data from files using Power Query in either Power BI or Excel you may encounter the following error: DataFormat.Error: External table is not in the expected format What causes it? TL;DR it’s because you’re trying to load data from one type of file, probably Excel (I don’t think you can get this error with any other source but I’m not sure), and actually connecting to a different type of file.

IT 194

IT 194

Chris Webb BI

AUGUST 20, 2023

A common requirement from Power BI customers in highly-regulated industries is the need to log users out of Power BI if they have been inactive for a certain amount of time. If your Power BI reports contain extremely sensitive data you don’t want someone to open a report, leave their desk for lunch, forget to lock their PC and let everyone in the office see what’s on their screen, for obvious reasons.

Security 187

Security Security Management IT 187

Chris Webb BI

AUGUST 13, 2023

One frequently asked question I see asked on Power BI forums is whether it’s possible to run Power BI Desktop on a Mac or indeed anything other than a Windows PC. There are already a lot of detailed blog posts and videos out there on this subject, such as this one from Guy In A Cube: the answer is no, you can’t run Power BI Desktop natively on a Mac or any other OS apart from Windows and there are no plans to port it over, so you need to either install Windows somehow (for example wi

Profiling 141

Profiling Win Management IT 141

Chris Webb BI

AUGUST 6, 2023

A few months ago a new option was added to the Sql.Database and Sql.Databases functions in Power Query in Power BI and Excel which allows Power Query queries that combine data from different SQL Server databases to fold. Here’s a simple example showing how to use it. On my local PC I have SQL Server installed and the Adventure Works DW 2017 and Contoso Retail DW sample databases: Both of these databases have date dimension tables called DimDate.

Retail 177

Retail IT 177

Chris Webb BI

JULY 30, 2023

In the second post in this series I discussed a KQL query that can be used to analyse Power BI refresh throughput at the partition level. However, if you remember back to the first post in this series, it’s actually possible to get much more detailed information on throughput by looking at the ProgressReportCurrent event, which fires once for every 10000 rows read during partition refresh.

Briefing 164

Briefing Information IT 164

Chris Webb BI

JULY 23, 2023

In the first post in this series I described the events in Log Analytics that can be used to understand throughput – the speed that Power BI can read from your dataset when importing data from it – during refresh. While the individual events are easy to understand when you look at a simple example they don’t make it easy to analyse the data in the real world, so here’s a KQL query that takes all the data from all these events and gives you one row per partition per refres

Loss 191

Loss Information IT 191