Append three tables without refreshing all

[Anonymous]

Hi,

I have three fact tables, one for 2020, second for 2021 and third for 2022.

Each table contains +10m rows and I want to have them as a one combined table in the PowerBI data model. However, to improve refresh performance would I only like to update the 2022 table while the others are not refreshed.

Is there any way of appending these tables in Power Query that will not trigger an refresh of all three tables once the appended tables is refreshed?

Any other solutions that would solve my needs (DAX UNION seems to be an alternative although often referred to as slow) are much appreciated.

Thanks!

Alexander

[BeaBF]

@Anonymous  

The best solution would be to move the data model to Tabular and create partitions, one for each year, so that you can trigger the refresh of the partition that refers to 2022, while having all three tables together in one with append.

have you ever dealt with tabular models? do you see a viable solution?

BF

[Anonymous · Accepted solution]

Hi @Anonymous ,

When you open Query Editor in power bi desktop, select the table that you do not want to refresh, right click it and disable Include in report refresh (it is active by default), close and apply Query Editor and the table will not refresh when the report is refreshed.

Best Regards,

Stephen Tao

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

[Anonymous]

Thanks for tip! I'm curious, will that work despite that these tables are imported into BI using several SQL-statements (Group by, Union, lead, lag etc)?

[serpiva64]

I deleted the post because i had some doubt and i wanted to do some trials. If i"ll get some result i will post it

[Daryl-Lynch-Bzy]

Hi @Anonymous - You need to configure Incremental Refresh.  To achieve this convert you "Year" column to "Start of Year" or "End of Year".  This refresh the current years partition only.

Incremental refresh for datasets and real-time data in Power BI - Power BI | Microsoft Docs