Press "Enter" to skip to content

Category: Power BI

Diagnosing Power Query Steps

Published 2019-12-30 by Kevin Feasel

Chris Webb takes us through the Diagnose Step button in Power Query:

As you might have guessed, it’s closely related to the Query Diagnostics functionality that was introduced back in October. Whereas the existing Query Diagnostics functionality allows you to see what happens inside the Power Query when a query is executed, this new feature does something similar but allows you to run a query up to a specific step. This is useful in scenarios where you want to reduce the diagnostics data you are collecting to a subset of the steps in the query without having to comment out a lot of M code.

It looks pretty useful.

Comments closed

Auditing the Power BI Activity Log

Published 2019-12-27 by Kevin Feasel

Brett Powell shows how to query the Power BI Activity Log:

The new Power BI Activity Log makes it much easier for Power BI administrators to access audit log data to monitor and analyze activities occurring within the tenant they support. This blog post describes one solution for accessing, processing, and loading Power BI activity log data as part of an automated process to support ongoing Power BI administrative analysis and reporting.

Brett has a very nicely defined solution and lays it out step by step for us.

Comments closed

Multi-Column Transformations with Power Query

Published 2019-12-23 by Kevin Feasel

Imke Feldmann shows how we can apply a function to multiple columns at once using Power Query:

You can apply simple transformations to multiple columns at once in Power Query using the UI only. In this article I show how you can apply advanced transformations on multiple columns at once instead. You can also use this to use custom functions instead. And lastly for the lazyefficient fans of custom M-functions: You will get a new “TranformAllMyColumnsAtOnceHowILikeIt”-function as well

Read on for a few examples, including one using custom code.

Comments closed

Copy Reports with Shared Data Sets Between Workspaces

Published 2019-12-17 by Kevin Feasel

Gilbert Quevauvilliers ran into a cross-environment issue:

I was working on some documentation for a customer and I came across a very quick and easy way to create a copy of a report which also creates a connection to the shared dataset that I could then copy to another “New Workspace”

Before I found out this gem, I had to manually do this via PowerShell which worked really well, but I had to do a whole lot of extra work to find the GUID’s then test it and make sure it works. With this new method it makes it simple and quick. It is a WIN-WIN

You can follow along as I show you how to do it below.

Click through for the demonstration.

Comments closed

Testing Power BI Report Performance in the Browser

Published 2019-12-17 by Kevin Feasel

Chris Webb continues a series on testing Power BI report performance in a browser. Part 2 walks us through some of the mechanics of the process:

Before you publish your report, in Power BI Desktop add a blank page with no visuals on to it. It doesn’t need to be the page that is opened when the report opens and you will be able to delete it later. Why do this? When you’re testing how long it takes for your report page to render, you’re probably doing so because you want to improve performance. Some things in the report page that influence performance you have the power to change, such as the design of the dataset, the DAX in the measures, the number and type of visuals on a page; some things will always happen when a report runs and you have to accept that overhead. Testing how long a blank page takes to render will give you an idea of how long this latter category of “things that always happen” takes, and you can subtract this time from the time your chosen report page takes to run.

Part 3 is a demonstration of the process:

…so you go ahead and publish. You view the report after publishing and it still seems fast. Then the complaints start coming in: the report is slow!?! It seems to be users who are viewing the report on their phone who are having the most problems. So, following the instructions in my last post, you open up Chrome DevTools and run an audit using a simulated slow 4G connection:

That’s an important part of testing. We normally develop inside a fast network, but our users may be on rather slow networks.

Comments closed

The Benefits of DAX Variables

Published 2019-12-17 by Kevin Feasel

Reza Rad explains why you should use DAX variables if you’re repeating calculations:

We have to main parts in the expression above: A and B. Each of those is doing a calculation. Now, with the markings above, reading the expression is much simpler. The whole expression means this:

=IF(A>B, A, B)

All the above expression is saying is that if A is bigger than B, then return A, otherwise B. Now it is much simpler to read it because we split the repetitive parts into sections. That is what exactly the DAX variable is for.

Readability is not the only benefit, however. Reza has more.

Comments closed

Conditional Formatting Line and Area Charts with Power BI

Published 2019-12-11 by Kevin Feasel

Soheil Bakkshi shows how we can conditionally format line and area charts with Power BI:

One of my customers asked me to show time series in line charts and area charts. But she want’s it to be conditionally formatted based on the average value over time. Let’s keep it simple, she wants to show “Sales by Year Month” in line chart, but, highlight the data points that are below “Average Sales per Year Month”. As you may know, we currently do not have the luxury of formatting line charts and area charts. But wait, this post is all about that. Let’s dig into it.

From the above scenario, you perhaps already guessed that we need to create a measure which defines the colour based on “Average Sales per Year Month” to be able to format the chart conditionally. If any data point is below the “Average Sales per Year Month” then we highlight it in Orange, if it is above the “Average Sales per Year Month” then we stick to the default colour.

Let’s do it.

This is definitely not straightforward, but once you see the process, it’s pretty neat.

Comments closed

Designing a Layered Donut Chart in Power BI

Published 2019-12-10 by Kevin Feasel

Prathy Kamasani shares how to build a layered donut chart in Power BI:

You can view the full report here – https://prathy.com/portfolio/school-uk/

The tricky bit of any good data visualisation is finding interesting data, inspiration and story. In my report, my story was to show the comparison of Ofsted school ratings among the total number of schools. I also wanted to carry my story between report pages with colours.

So in this particular visual, I wanted to make it visually appealing, so I decided to show two metrics on each donut visual, the measure I want and the total number of schools. Then I resized each visual in a way so that they look like one visual. Those days there were no grouping, but now we can group them as one visual too. Also, when I first designed this report, default Power BI donut chart didn’t have an option to resize the ring, so I went for Circular gauge by MAQ software. Now we can use the default Donut chart too.

Click through for more details.

Comments closed

Time Series Anomaly Detection with Power BI

Published 2019-12-09 by Kevin Feasel

Leila Etaati takes us through time series anomaly detection with Cognitive Services and Power Query:

I am excited about this blog post, this is based on the New service in Cognitive Service name “Anomaly Detection” which is now in Preview.
I recorded a video about how it works in cognitive service https://youtu.be/7ZOtZDbn6gM. 

However, I am going to talk about how to use it in Power BI. In this post first, a brief introduction to the anomaly detection will be presented, then how it can be used inside Power BI will be discussed.

It sounds like there are still some rough edges, but they already have the makings of an interesting service.

Comments closed