Appending Queries & Refresh

[datapal04]

Hello,

I am developing a Power BI report based on CRM data. Our team has been told to use a new CRM system - however some of the data in this new CRM system is still not accurate, so I am querying data from the old CRM system database as well. My scenario has the below queries:

1. Old CRM database for data from Jan 1, 2019-Nov 30, 2022.
2. New CRM database for data after Nov 30, 2022. 
3. Appended query for 1. and 2. (done in Power Query).

The data/columns are the same, but I would like to use only the appended query (which contains all data) in my modeling and building out the report.

My concern is regarding refresh. I would like to implement incremental refresh on the new CRM query since new data will always be added. FYI old CRM data is static - I only use the query to bring all the data I need into Power BI because at some point the old CRM will be decommisioned.

How can I have the appended query (3) only refresh data for the new CRM? In other words, can I set incremental refresh on the new CRM query (2) and not have a call to the old CRM (1) since I have all the data into Power BI already? I hope this makes sense and please let me know if I need to clarify. Thanks!

[DougieK]

@datapal04 I have the same challenge with the added complexity of a native query to SQL Server for both 1 and 2.  Finally got IR to work on 2 with Advanced Editor & SQL injection - passing in Text.From(RangeStart/End) to the where clause.  However, appending kills IR for me in 3.  

@edhans Thanks so much for the insight here.  Figured the Table.Combine() re append was the issue, and I think you're eluding to it there but keeping the fact tables separate and using something like Union in Dax (if that's even possible) isn't ideal. 

In my case, I have a bunch of New Columns in the appended table so I need a final fact table to use. 

Maybe if my Native Query (2 in pal's situ) queried source tables in a Dataflow setup with IR I'd be fine.  But I think Dataflows require Premium and I need a Pro solution.

[edhans]

I do not believe so, but would like to be shown wrong. The reason is the Table.Combine() function often doesn't fold, which is required for incremental refresh. Your best bet will be to stage this data in a server (SQL Server for example) then create a view that has all 3 in one table, then set up IR on that view.

You could load the historical info in another table and set it to not refresh, but that splits your fact table and makes the DAX both messy and slower as you start working.