MB-260: Configure incremental refresh

You can configure the incremental refresh after you’ve transformed the data in Power Query, you can also do this by editing the source after you’ve finished. Maybe you’ve set it up ready, but after reading about incremental refresh, you realise how smart it is and you want to edit your data source refresh.

Microsoft Power Query (MPQ)

To be able to use incremental refresh with MPQ you need to set three fields:

• Primary key – This is the key you’re using to make sure the rows you have in your table is unique.
• Last updated field – Which column in your table decides when the data was last updated.
• Check for updates every – How often do you want to update this particular data source.

After this, your data is fully updated once, and then the new data is updated when you’ve set the time for updates.

Azure Data Lake Storage (ADLS)

Where you can use Power Query to do incremental updates, Microsoft recommends that you use ADLS. It’s the preferred solution and it’s out of preview.

For an Azure Data Lake Storage, there are two fields you need to set

• FullData – This is the folder where you have all your data
• Incremental data – These are the folders that controll what and when your data will be updated. These will be controlled by a time setup and then by the folders upsert or deleted. The folders must be in yyyy/mm/dd/hh setup, this blog is posted at 09.00 CET March 8 2024, so the folder for this post to be updated would be 2024/03/08/09 (4 numbers for the year, 2 numbers for month, day and hour). For each of the times, you’ll have folders for updating and deleting your data.

When you set this up in the system it must be set up like the picture:

Also with the ADLS you’ll have to run one full refresh of the data before you can start the incremental refresh.