Forum Discussion
Incremental Refreshing
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
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
9 Replies
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
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
Can you explain this part :
I really can't select my parameter. Its's defined under Other Queries.
Hi,
Can I apply incremental refresh to just one table and have it refresh other tables in the schema as well?
