How to stop refreshing of older append files?

[MC_2023]

Dear, 

I've used the Append feature in Power Query Editor to combine sales data in Power BI, see below:

While I got few years of sales data, it takes longer and longer time to refresh the report. Is there any way that can only referesh the data of the latest file? Appreciate someone can help on this issue.

Thanks in advance!

[v-tsaipranay · Accepted solution]

Hi @MC_2023 ,

Thank you for reaching out to Microsoft fabric community forum regarding performance issues with your Power BI report due to the lengthy refresh times when using the Append feature to combine years of sales data.

To ensure your report refreshes efficiently while maintaining access to the latest data, you can implement the following approaches:

• Incremental Refresh allows you to refresh only new or modified data instead of reloading the entire dataset. This is particularly useful for large datasets like yours.
• In the Power Query Editor, create separate queries for historical and recent data. Append these queries together, and disable the refresh for the historical query by unchecking "Include in report refresh" under query properties.
• To ensure your queries are efficient, remove unnecessary columns or rows, use query folding to push transformations back to the source database whenever possible, and avoid complex transformations in Power Query.
• If the older years of sales data are not necessary for analysis, please filter them out in Power Query Editor before appending the datasets.

I hope my suggestions give you good ideas, if you need any further assistance, feel free to reach out.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thank you. 

[v-tsaipranay]

Hi @MC_2023, 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-tsaipranay]

Hello @MC_2023 ,

I wanted to follow up on our previous suggestions regarding how to stop refreshing of older append files. We would love to hear back from you to ensure we can assist you further.

If my response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.

Thank you.

[v-tsaipranay]

Hi @MC_2023 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Ilya_K]

Hi,

Incremental refresh was already mentioned and that's the only option to do that.

However, I would like to point out two things.

1. Truly requirements. You need to check with consumer of the report what kind of historical data do they really need. Usually, stakeholders say "we need everything", but in fact it is used very seldom. Maybe you can schedule some reports to update on monthly/quarterly or even annual basis. Then you can have, let's say, 24 Months sales history update every day and 5 years history only once per quarter.

2. Granularity. If stakeholders do not need to see daily transactions in 2018, you might aggregate data by month or even years. Like that the size of file will be reduced drastically. Based on my experience, for example, even highly unpredictable demand in aftermarket business usually relies on 2-3 years of history.

[AnkitKukreja]

Hi! @MC_2023 

Incremental refresh would be the best approach, but you can give this a try as well.

• Identify your historical files or data up to a specific date range.
• In Power Query, load these files into a query named, for example, HistoricalData
• Once loaded, disable load for this query by right-clicking it in the Query Editor and selecting Enable Load (uncheck it).
• You can save this data as a static file (e.g., a CSV or Excel file) in a secure location.
• This step helps if you want to completely remove older data files from Power BI.

•  

[ZhangKun]

https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview

If your data is allowed to be stored in the cloud, you can use incremental refresh.

In fact, incremental refresh is implemented through hot and cold partitions, but for some reason, it is not allowed to be used locally (and cannot be used).

Another method is also relatively simple, import historical data into the local SQL server, and then merge the data in the SQL server (historical data) and the data in the Excel file (new data) in Power Query.

No matter what you do, please pay attention to the commercial license of the software.

[Akash_Varuna]

HI You could use Incremental refresh which only refreshes the only new data or could use Dtaflow which is like power query but runs externaly before loading into power bi for managing historical data or filter data in power query itself