Update status of query that merges two other queries

[SimonAurizon]

Hi everyone,

I have two queries 'FactSales2023' and 'FactSales2024'. For 'FactSales2023' I have unticked "Include in report refresh", whereas for 'FactSales2024' I have left it ticked. Nothing complicated so far: when I perform a whole report refresh, 'FactSales2024' updates and 'FactSales2023' doesn't.

Now I created another query 'FactSalesMerged' that is simply Source = Table.Combine({FactSales2023, FactSales2024}). For 'FactSalesMerged' I have left "Include in report refresh" ticked.

What's the expected behaviour when a whole report refresh is performed? I was hoping 'FactSales2024' would refresh, 'FactSales2023' wouldn't refresh and 'FactSalesMerged' would union the unrefreshed lines from 'FactSales2023' with the refreshed lines of 'FactSales2024'. But inserting a custom column with DateTimeZone.UtcNow() into 'FactSales2023' and 'FactSales2024' to test that appears to show that, whilst 'FactSales2023' didn't refresh (as expected), its lines appearing in 'FactSalesMerged' did in fact refresh... could that be?

[SimonAurizon]

Thanks @Ritaf1983.

I'm not a huge fan of the currently-available incremental refresh options as they only work on the service and I do like to refresh my Powe BI Desktop .pbix sometimes, but without refreshing every line of previous years' data.

A method I've used before it to just have two queries 'FactSales2023' (not included in report refresh) and 'FactSales2024' (included in report refresh) and then create a calculated table in DAX that uses UNION to join them together. It works, but my understanding is that is always better to push as much work back to Power Query as possible rather than using DAX, so this runs counter to that. Annoying that it doesn't seem possible to achieve this in Power Query currently.

[Ritaf1983]

Hi @SimonAurizon 

The Disable options does not avoid your table from being refresh it just makes it not load the data into Power BI in this case since it's being used on another table you see it when it's refresh.

To make this partial refresh make you need to use incremental refresh, however be aware of how you implement it because it can give you some others issues if the setup is incorrect.

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

https://www.sqlshack.com/an-overview-of-power-bi-incremental-refresh/

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