Appending Two Tables - One With Incremental Refresh Policy, and One With Full Load

[Dave1mo1]

Apologies if this isn't the correct forum. I am at a loss with this issue.

I have two queries:

1. All invoiced orders down to the line level for the last several years pulled from an Oracle database view. This is a large data set and the historical data is unchanging, so I have an incremental refresh policy set up on this table to refresh the last 60 days based on invoice date (in case an invoice is missed, for whatever reason). I've set up incremental refresh policies before with no problem.

2. I have a second query pulled from a separate Oracle database view. This query shows all open orders (no invoice date) for the last 2 years. It's a small data set, and we have orders get invoiced (and end up in the other query), get canceled, etc. I want to load this entire data set with refresh.

Via the Power Query experience I harmonize any fields between the two views that have different spellings, data types, etc. I then want to append the two queries into one fact table for my analysis (I "append as new query"). I am able to do so in Power BI Desktop with no issue. I have one fact table, and my "Totals" measures match with what I'd expect. However, when I publish this semantic model to PBI Service and refresh, something with the append seems to be off. The historical data is loaded as I'd expect, but my appended fact table still only has the sums from my abbreviated incremental refresh parameters (I set it to the last 30 days in the incremental refresh parameters). 

I created a test measure to sum some values based just on the incrementally refreshed table (the large invoicing table), and that is showing totals going back several years. However, the measures that are based on my appended table are only showing values based on the original incremental refresh parameters in the Desktop file. The incremental refresh is loading the data, but for some reason the data isn't ending up in the final, "appended as new" table.

Can anyone help with this issue? I've spent two days intermittently trying to resolve it on my own and feel like I'm just smacking my head against the wall. Thank you!!!

[Aburar_123 · Accepted solution]

Hi @Dave1mo1 ,

It might be becuase the incremental setting is not transferred to the appended table. So, in Power query, it takes that filtered data.ie., the between RangeStart and RangeEnd dates.

In this case, just try the below steps that may work.

1. Take a reference from your second table that requires full refresh

2. then append your first table (incremental refresh table) with the referenced table taken in #1

3. then apply the incremental refresh on that appended table.

Thanks.

[v-pgoloju]

Hi @Dave1mo1,

Just following up to see if the Response provided by community member were helpful in addressing the issue. If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[v-pgoloju]

Hi @Dave1mo1,

Just following up to see if the Response provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

Best regards,

Prasanna Kumar

[v-pgoloju]

Hi @Dave1mo1,

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to @Aburar_123  for prompt and helpful response.

Just following up to see if the Response provided by community member were helpful in addressing the issue. If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[Dave1mo1]

Thanks for the response. Does it matter if I "Append" or "append as new?"

[Aburar_123 · Accepted solution]

both are doing Append but Append as new will take a separate copy and append the data.

[Aburar_123 · Accepted solution]

Hi @Dave1mo1 ,

It might be becuase the incremental setting is not transferred to the appended table. So, in Power query, it takes that filtered data.ie., the between RangeStart and RangeEnd dates.

In this case, just try the below steps that may work.

1. Take a reference from your second table that requires full refresh

2. then append your first table (incremental refresh table) with the referenced table taken in #1

3. then apply the incremental refresh on that appended table.

Thanks.

[Dave1mo1]

This is working to get the table with the incremental load data correctly into the model (thank you!), but it looks like it's duplicating the full-load table across multiple partitions. Any thoughts on that? Otherwise I might just split the "incremental load" table into a "Historical Table," load it once, and disable the data refresh, then load the same table a second time starting from the end date of the "Historical Table" and load that table fully each time. This would essentially remove the need to have the incremental refresh policy, but it's not a long-term solution once the second query becomes so large that it slows down the refresh.

Not sure how else to handle this. Someone on another forum suggested doing a union in DAX to create a calculated table, but that would bloat the size of the model by loading the tables twice (once initially, then a second time when they're unioned), and I would need to have the exact same fields in both tables. Most fields are the same, but there are a few fields that are unique to one table or the other.

Any additional thoughts? Really appreciate your help, by the way.