Following on from my successful blog post How you can incrementally refresh any Power BI data source (This example is a CSV File), I found a way where I can just use dates created in Power Query to get data refreshing incrementally.

Full credit goes to Rafael Mendonç who actually figured this out. All that I have done is to translate what Rafael Mendonça did in his PBIX and put it into steps that you can follow along with.

https://www.rafaelmendonca.com/2020/06/incremental-powerbi-csv-api-excel-odbc.html

In this blog post I am going to demonstrate how to get this working with what I hope is very easy to follow. As well as you can download the PBIX at the end of the blog post, which you can use as your template going forward.

In my example I am going to have a start date of 01 June 2020 (which is when my data will start from)

I am going to initially configure the RangeStart and RangeEnd Parameters to be 01 June 2020 to 03 June 2020

Once I have completed the configurations and successfully refreshed the dataset, I will have dates and partitions for the past 20 days

Configuring the Parameters

  • I go into Power Query Editor
  • I configure StartDate Parameter to be 2020-06-01
    • NOTE: Change this start date in your data to be when your data begins.
  • I then configure my RangeStart to be 2020-06-01
    • A quick note even though I did not put in the time Power Query is smart enough to put those in for me.
  • I then configure my RangeEnd to be 2020-06-03
    • A quick note even though I did not put in the time Power Query is smart enough to put those in for me.
  • I verified that my parameters were as expected

Configuration of my Data table

Following on from above is where I have my data table, which is where I configure the RangeStart and RangeEnd Parameters

  • As you can see below, I configured my column to use the RangeStart and RangeEnd Parameters
  • And as shown above I can see the 2 dates I expect to be based on my parameters.
  • As with my previous blog post, I could then add in additional columns in this table which will give me the required date column which I can join to any existing dataset.
    • This will then allow my dataset to be refreshed incrementally.
  • One additional step I had to configure to get the incremental refresh to work is I added another table which has an actual data source.
    • This table gets an Exchange Rate from a Website
  • I had to create this table. Without any actual data sources in my dataset Power BI actually has nothing to refresh.
  • I then loaded the dataset.
  • This is what it looked like before the incremental refreshing

Testing the Incremental Refreshing

The steps below are where I configured and tested the incremental refreshing to make sure it works as expected.

  • I configured my data table with the following incremental refreshing policy for the post 20 days and to refresh the past 2 days
  • I then uploaded my dataset to the Power BI Service (Again using Power BI Premium so I could double check the refreshing and partitions!)
  • Next, I completed the first dataset refresh which should create the partitions.
    • I verified this using SSMS and I could see the last 20 days worth of partitions
  • Now was the final step to see it it will only refresh the last 2 days worth of partitions. I went and refreshed the dataset again.
  • I refreshed the partitions in SSMS, and I could now see the last 2 days of partitions were refreshed.
  • Finally, I could see the table has been updated and incrementally refreshed.

Summary

I must admit for me personally this is even better than my previous blog post, because there is no need to create, maintain and look after an Azure SQL Server database and table.

The pattern to achieve this with your dataset can be summarized as the following:

  • Copy the parameters and data table.
  • Edit the StartDate parameter to match the start date of your data.
  • Create a column in the data table which has a date or date time which you can use with your data.
  • Join your dataset to the data table using a function or a merge
  • Load the dataset
  • Configure the Incremental refreshing.
  • Upload your PBIX, refresh once, then schedule the refresh and you now have your incremental refresh working.

I hope that this has been useful and if you have got any questions please leave them in the comments section below.

Here is a link to the PBIX: Incremental Refresh with Power Query.pbix