Press "Enter" to skip to content

Category: Power BI

Importing Data from ADLS Gen2 into Power BI

Published 2021-04-14 by Kevin Feasel

Chris Webb summarizes a significant amount of work:

Over the last few months I’ve written a series of posts looking at different aspects of one question: what is the best way to import data from ADLSgen2 storage into a Power BI dataset? For example, is Parquet really better than CSV? Should you use Azure Synapse Serverless? In this post I’m going to summarise my findings and offer some recommendations – although, as always, I need to stress that these are the conclusions I can draw from my test results and not the absolute, incontrovertible “Microsoft-says” truth so please do your own testing too.

Read on and check it out for yourself.

Comments closed

Power BI Cross-Report Drill-Through

Published 2021-04-14 by Kevin Feasel

Marc Lelijveld takes us through the benefits and challenges of drilling through to a different report in Power BI:

It is very common to have multiple reports for different audiences, while there is also one (group of) user(s) that requires to have an overview over all these different insights. The main challenge you will face, is having cross-report interactivity and find related insights.

Let’s take an example of three different roles, where we have a customer account manager, reseller manager and a regional manager. Of course, they should have the same single source of truth, but there is one thing you want to avoid as report creator! You do not want to create three different report for the three mentioned audiences. But as they have different roles and responsibilities, you do not want them to see each other’s data and keep it clean and simple! In this blog I will describe how you can setup cross-report drill through to jump from one report to another, while respecting applied filters and avoiding building three separate reports!

Click through for the process, as well as potential issues you may hit along the way.

Comments closed

Star Schemas and Power BI Go Together

Published 2021-04-13 by Kevin Feasel

Marco Russo and Alberto Ferrari explain why star schemas make so much sense for Power BI:

Why should I have products, sales, date and customers as separate tables? Wouldn’t it be better to store everything in a single table named Sales that contains all the information? After all, every query I will ever run will always start from Sales. By storing everything in a single table, I avoid paying the price of relationships at query time, therefore my model will be faster.

There are multiple reasons why a single, large table is not better than a star schema. Here anyway, the focus is strictly on performance. Is it true that a single table is faster than a star schema? After all, we all know that joining two tables is an expensive operation. So it seems reasonable to think that removing the problem of joins ends up in the model being faster. Besides, with the advent of NOSQL and big data, there are so many so-called data lakes holding information within one single table… Isn’t it tempting to use those data sources without any transformation?

Read on to see why this is not the case.

Comments closed

Equalizing Matrix Column Widths in Power BI

Published 2021-04-12 by Kevin Feasel

Nick Edwards has bad news and a workaround:

Have you ever come across an issue where your Power BI matrix column widths just aren’t the same width and visually just don’t look right?

Unfortunately (as of April 21’) there is no easy way to make all column widths equal in the format pane of a matrix visual.

However there is a hack to set the width of your all columns in a matrix so that they are all equal and pixel perfect with DAX!

Click through to see how. It’s a hack in all the finest ways: clever, unexpected, and a bit unwieldy.

Comments closed

Power BI Content Certification and Promotion

Published 2021-04-07 by Kevin Feasel

Melissa Coates talks about certification (though not certifications):

The number of people who are allowed to certify content should be pretty limited, and should only include people who are knowledgeable about both the data and the certification requirements. Ideally, certification should only be done by domain/subject matter experts — and this isn’t usually your Power BI administrator. In larger organizations, you might reference multiple Azure AD security groups in the tenant setting – but it should remain a pretty limited number of people to ensure that the certification designation is truly meaningful. (Note that we currently cannot specify who can endorse content on a per-workspace level.)

Read on for more information about content certification, as well as content promotion, in Power BI.

Comments closed

Power BI Exporter

Published 2021-04-07 by Kevin Feasel

Soheil Bakhshi announces a new community tool:

Apart from my website statistics showing many people want to export data from Power BI Desktop, some of my customers asked the same question. They wanted to export the curated data from their data model within Power BI Desktop to CSV format and make the curated data available for their other platforms. While all the methods mentioned in my previous blog posts are working, some users still find them complex. So I thought, we can make it better. We can make a straightforward tool that exports the data with only two clicks. So we started building the Power BI Exporter as a micro-project. We added some more ideas to the original idea of only exporting the data. We thought it is good to export the data along with the table names, column names and relationships. Having that information handy, we can quickly build the same data model as the one we exported its data but using the CSV files as the data sources. The other idea was to pack everything in a ZIP file on the fly, so we have a single ZIP file, including the tables, columns, and relationships. As a result, the first version of the Power BI Exporter is born. In this post I explain how it works.

Read on to see how to use it. Download is free, though you do have to provide them with an e-mail address.

Comments closed

Granular Deployment of Power BI Changes with ALM Toolkit

Published 2021-04-07 by Kevin Feasel

Gilbert Quevauvilliers shows off an interesting scenario:

In this blog post I am going to demonstrate how to make a granular deployment where I will create a new column in my City table, and only deploy those changes.

What this means is that by deploying only the column change to my PPU dataset, I am only updating the column in the table.

This now saves me from doing the following tasks previously:

– Time taken to refresh the PBIX file so that the data is up to date.
– Re-uploading my PBIX.
– If configured re-creating the incremental refreshing
– Time and effort to upload and wait for dataset refresh.
– Quick updates to my dataset.

I will not have to worry about saving my PBIX file, file and if configured re-creating the incremental refreshing. This saves me a lot of time and effort.

Click through to see those steps in action.

Comments closed

Comparing CSV to Parquet File Loading Performance in Power BI

Published 2021-04-05 by Kevin Feasel

Chris Webb has a comparison for us:

Earlier in this series on importing data from ADLSgen2 into Power BI I showed how partitioning a table in your dataset can improve refresh performance. In that post I used CSV files in ADLSgen2 as my source and created one partition per CSV file, but after my recent discovery that importing data from multiple Parquet files can be tuned to be a lot faster than importing data from CSV files, I decided to try creating partitions linked to Parquet files instead.

Click through for the experiment and its results.

Comments closed

Power Query and the First Day of the Week

Published 2021-04-02 by Kevin Feasel

Ed Hansberry decrees that henceforth, Thursday shall be the first day of the week:

By default, Power BI starts its week on Sunday, but that doesn’t work for every situation. Some companies prefer their week start on Monday, and I recently helped someone that needed their week to start on Thursday. Fortunately, by tweaking your date table in Power Query, you can make this change with no calculations at all.

Click through to see how.

Comments closed