I have to say that I am quite fortunate to be a Microsoft MVP, and I am very grateful for the opportunity. This was one of those times when I was reading an email from Christian Wade and Christian had given me a little piece of gold.

What is that gold you might wonder, well it is that there is a way to change the partitions of your data in Power BI are stored. For example, if you want it to be stored Monthly there is a way to do this. Likewise, if you want it to be stored by Quarter you can do this to.

NOTE: One reason why this might be applicable is when data is partitioned by month the query that gets executed will be for each month. Whilst if the query is partitioned by year it will then execute the query for an entire year. This can lead to a really long time for the query to complete.

NOTE II: Currently the default timeout for a query to complete is 5 hours (Which is a long time, but might not be enough time if your dataset is large)

I will show you how I did it below.

My goal is the following:

  • Have all the historical data partitioned by Month.
  • The last 2 days be partitioned Daily.
  • I used the awesome new Power BI Premium Per User functionality, which would allow me to see the partitions.

How I achieved my goal

For all of this to work I had to configure the Incremental refresh in Power BI Desktop

  • The first thing I did was to make sure that I had set up my incremental refresh in Power Query correctly.
  • I then right clicked on my table where I had configured the Incremental Refresh.
  • I then configured it with the following settings as shown below.
  • I saved my PBIX file and uploaded it to the Power BI Service.
  • I then configured the dataset settings and kicked off the dataset refresh, by clicking Refresh Now
  • Once the dataset refresh was complete, I first went and validated that my Power BI Report had got all the data. And as shown below it had all the data.
  • Now for me to see how the tables are partitioned I opened DAX Studio and connected to my Power BI Premium Per User App Workspace.
    • I then clicked on Advanced and then View Metrics to get all the Metrics of my dataset.
  • What I saw below was that all my historical partitions where partitioned by month with the naming convention of YYYYQQMM
    • For Example, for the Month of Oct 2019 it had the Partition Name of 2019Q410
  • And then for the past 2 days or as per my screenshot above the past month, it has got it partitioned by day. Which allows only the last 2 days worth of data to be refreshed.
    • This also has got the naming convention of YYYYQQMMDD
    • For example, for the date of 01 Nov 2020 it had the partition name if 2020Q41101
  • Now that is really cool, it allows me to control the granularity and how I want my partitions to be configured.
  • I also had a look to see how much memory my dataset was consuming and as shown below it was using just 42MB!

Different Partition Configuration Options

My working example was just one of the many variations on how the partitions can be configured. Below I show you what other combinations you can complete.

Yearly Partitions and Monthly Refresh

Below is where the historical data would be partitioned by Year and the refreshed data monthly.

As I have shown below this is what the partitions look like once configured.

And when looking at the dataset memory consumption it is almost the same as previous configurations

Yearly Partitions and Daily Refresh

Below is where the historical data would be partitioned by Year and the refreshed data daily.

As I have shown below this is what the partitions look like once configured.

And when looking at the dataset memory consumption it is almost the same as previous configurations

Monthly Partitions and Monthly Refresh

Below is where the historical data would be partitioned by Month and the refreshed data monthly.

As I have shown below this is what the partitions look like once configured.

And when looking at the dataset memory consumption it is almost the same as previous configurations

Quarterly Partitions and Quater Refresh

Below is where the historical data would be partitioned by Year and the refreshed data monthly.

As I have shown below this is what the partitions look like once configured.

And when looking at the dataset memory consumption it is almost the same as previous configurations

Summary

In this blog post I have shown that you can vary the different types of partitions in Power BI with Incremental refresh, which will allow you to configure it as per your requirements.

I personally found depending on the dataset and the underlying query performance I have had to alter the partition to make sure the data can be loaded quick enough.

This will be my final blog post for the year. Thanks for reading along with me this year, I hope that you found the content helpful. As always, any comments or suggestions are always welcome.