Press "Enter" to skip to content

Category: Power BI

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

Optimizing Power BI Data Load from a Folder of Parquet Files

Published 2021-03-31 by Kevin Feasel

Chris Webb has a tip for us:

In all the testing I’ve done recently with importing data from Parquet files into Power BI I noticed something strange: loading data from a folder containing multiple Parquet files seemed a lot slower than I would expect, based on the time taken to load data from a single file. So I wondered – is there something that can be optimised? It turns out there is and in this blog post I’ll show you what I did.

Click through to see how Chris cut load time down to approximately half what it was.

Comments closed

Building a P&L Statement in Power BI

Published 2021-03-23 by Kevin Feasel

Matt Allington has a follow-up from a prior post:

I blogged about how to Build a P&L With Power BI back in April 2020, and the response from my readers was great. Several people asked how to add percentages such as % of net sales revenue, % gross profit, etc. into the P&L. I decided therefore to do a follow up article to show how you could add percentages to the P&L and also to further demonstrate how I go about solving such problems. Keep in mind, there is rarely one way to solve a problem in DAX; I’m simply sharing the way I solved this problem.

Click through for a video as well as a demo in screenshots.

Comments closed

Parquet File Performance in Power Query

Published 2021-03-22 by Kevin Feasel

Chris Webb troubleshoots a performance issue:

There has been a lot of excitement around the newly-added support for reading from Parquet files in Power BI. However I have to admit that I was disappointed not to see any big improvements in performance when reading data from Parquet compared to reading data from CSV (for example, see here) when I first started testing it. So, is Power Query able to take advantage of Parquet’s columnar storage when reading data?

The answer is yes, but you may need to make some changes to your Power Query queries to ensure you get the best possible performance. Using the same data that I have been using in my recent series of posts on importing data from ADLSgen2, I took a single 10.1MB Parquet file and downloaded it to my PC.

It seem like an area of future growth for Power Query, but Chris does show how to eke out some gains right now.

Comments closed

Common Power BI Administration Scripts

Published 2021-03-17 by Kevin Feasel

Brett Powell continues a series:

Today’s post builds on top of Power BI Admin Scripts Part I by describing five additional PowerShell scripts that Power BI service administrators can utilize to address relatively common scenarios. Like Part I, the five new scripts have been added to my GitHub repository and I’ll only share context in the blog.

Please be sure to read through the prerequisites section of Part I and confirm you have the necessary permissions (e.g. Power BI Admin role) and software installed such as the latest Power BI Management PowerShell modules.

Check it out for five more scripts. Brett also teases a part 3, but you have to wait until after the credits sequence to see it.

Comments closed

Thinking About Power BI Premium Per User

Published 2021-03-16 by Kevin Feasel

Martin Schoombee has an analysis for us:

When Power BI Premium was released in June 2017, many of us felt left out as the jump from Pro to Premium was just too high…unless you were an Enterprise-level customer that could afford the ~$5,000/month price tag. The economics just didn’t make sense for most people, and the size limitations of Power BI Pro was still a concern to companies who wanted to jump on board.

At Microsoft Ignite this year, we finally got the answer we were waiting for. The pricing for Power BI PPU (Premium Per User) was announced and will be generally available in April 2021. With the price set at $20/user per month, which is better than I expected, I think the landscape is going to change in a significant way. Let me explain…

I’m tied in reasonably well to the Power BI blogging space and I’m a bit shocked that Power BI Premium Per User hasn’t been the recipient of a lot more excitement. I typically live on the budget side of things and disliked the $5K entry point for Premium. At $20 per user, I’m quite happy. Martin does point out a third scenario, however: using Azure Analysis Services plus Power BI Professional. But even in his chart, I think I’m still picking Premium Per User over AAS + Pro due to the additional functionality at approximately the same price.

Comments closed