Incremental Refreshing

[Katiek]

Hi, 

I'm a bit stuck in my understanding of incremental refreshing. In transform data, I've managed to import 3 yrs (2022-2024) worth of data excel data tables and combine them. The problem I now have, when i go to refresh that data it takes more than 6hrs to update. In reality though, I don't need to update all 3yrs monthly. The data in Years 2022-2023 don't change anymore. Just the data in 2024 changes. So I just want to update the excel named 2024 monthly but still have the 2022 and 2023 data.

I was hoping to incrementally refresh the 2024 data based on the Date modified column. Because we have an excel in sharepoint and continually update this from scratch every month, because data from the previous month could have changed.

I created the RangeStart and RangeEnd Parameters. However, when I go to filter it in my date modified colum and select user defined filter I can only select the dates and times and not my parameters. 

My question is incremental refresh the right thing for what I want to do, just refreh the 2024 excel file from sharepoint monthly?

Thanks for your help

KatieK

[AmiraBedh · Accepted solution]

Yes you can use incremental refresh in your case. In PQ, go to the table with your data and filter the Date Modified column.
Instead of manually selecting dates, you should be able to apply a filter using the RangeStart and RangeEnd parameters. Then choose Custom Filter and set it to filter for records where Date Modified is greater than or equal to RangeStart and less than RangeEnd.

 After setting the filter in Power Query, close and apply the changes.
Then, set up the incremental refresh for your 2024 data. Define the period for incremental refresh (refresh the last 1 year) and specify how long you want to keep historical data (keep data for 3 years).

You can configure how much data to refresh and whether to archive data. For example, you could set it to refresh the data for the past 1 year (which would cover the changing data for 2024) while keeping the historical data for 2022-2023 unchanged.

If you want to be more specific and only refresh the most recent data, you could set this to 1 month if you only expect data changes for the most recent month.

For example, if you update your data on the 1st of each month, setting this to 1 month will ensure that only the last 30 days of 2024 are refreshed, making the refresh process faster. However, this assumes that older data in 2024 does not change.

Update :

The solution was to convert the column to Datetime

[choudhari_mayur]

Hi,
Can I apply incremental refresh to just one table and have it refresh other tables in the schema as well?

[Katiek]

Hi Amira

Thanks so much for your quick reply.

I really can't select my parameter. Its's defined under Other Queries.

But when I go to my table to filter on the date modified colum and select user defined filter it only shows me the dates in my modified colum and not the option for parameters

I'm not sure what I'm doing wrong

[AmiraBedh]

Can  you explain this part : 

I really can't select my parameter. Its's defined under Other Queries.

[Katiek]

It's just in a folder called Other queries. 

They way that I have my data set up is that I have in the folder Other Queries, one table  which imports and combines the data, and then under the folder Data Model I have the Table with the actual data, which is directly linked to the table which imports and combines the data.

[Katiek]

Hi @AmiraBedh 

I just figured out why I couldn't see the parameter option. My date field that I wanted to foliter wasn't set up as Date/Time. OMG. Thanks so much for your  help and quick replies. I'm going to try setting it up now. 

Thanks 

KatieK

[v-rzhou-msft]

Hi @Katiek ,

Could you tell me if your issue has been solved? If it is, kindly Accept the helpful reply as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your issue.

For more, I will give you some suggestions.

You can follow the steps in this offical blog to configure incremental refresh.

Configure incremental refresh and real-time data for Power BI semantic models - Power BI | Microsoft...

If you want the incremental refresh show good performance, please make sure whether your data source support query folding. You can right click the steps in Power Query and check whether the "View Native Query" is available.

If not, it may show a bad performance and take a long time in refreshing.

Query folding guidance in Power BI Desktop - Power BI | Microsoft Learn

Understanding query evaluation and query folding in Power Query - Power Query | Microsoft Learn

Troubleshoot incremental refresh and real-time data - Power BI | Microsoft Learn

Best Regards,

Rico Zhou

[Katiek]

Sorry for the late reply, but the marked solution as accepted, die help me. Thanks for your help! 

[AmiraBedh]

If my answer helped you don't forget to accept it 🙂