Forum Discussion
incremental refresh on table by reference
Hi, in my data model I pull a table from sql server and then after some transformations on power query I copy this table by reference.
So I have two tables with the same data (only that one is set as a reference to the other).
Now I want to set the incremental refresh, so:
- I set the two parameters RangeStart and RangeEnd with type Datetime
- I insert a step to filter the original table so it filters the table between RangeStart (>=) and RangeEnd (<)
As the final step I go the original table and I enable de incremental refresh setting to load the full data of the last 10 years and to reload only the last 3 months.
I tried to set the incremental refresh on the table set by reference but I can't because power bi desktop says I must filter the table using the parameters (which in theory is done as it's a table by reference).
When I publish to the web and do a full reload the original table works as expected. It is loading the full data and subsequent refreshes only reloads the last two months.
But the table copied by reference is only showing the last two months all the time.
¿Any ideas on how to fix this?
8 Replies
I have a workaround that worked for my similar problem: In each query that references the main query, I set some dummy variables to RangeStart and RangeEnd. This tricked PowerBI into letting me enable incremental refresh on the referencing queries. Now the referencing queries are refreshed along with the main query in the dataset.
This is brilliant. THANK YOU!!
I used the Advanced Editor in Power Query to edit the code of the query. This is the first three lines of the dependent/reference query:
let
vStart = DateTime.ToText(RangeStart,[Format="yyyy-MM-dd'T'HH:mm:ss'Z'", Culture="en-US"]),
vEnd = DateTime.ToText(RangeEnd,[Format="yyyy-MM-dd'T'HH:mm:ss'Z'", Culture="en-US"]),One key piece is to configure Incremental Refresh ONLY on the dependent/reference table. I did NOT apply it to the source query.
I was also getting an error saying "column does not exist in the rowset" in the service when trying to refresh. I resolved this by DELETING the file and republishing it.
Anonymous it is really interesting question. I think I know the answer but have to find out best way to explain this. I will try to reproduce this on my end and then come up with an answer, it might take a while thou 🙂
Hi there,
I have the exactly same need, my main table A (thats pull data from SQL Server) is being referenced 2x times (by tables B and C), but once I set the incremental refresh on table A the dependent tables, B and C, have just records associated to filter range defined on PBI desktop. Seems that I have to setup the incremental on tables B and C also, but as these tables are grouping data they do not have the date column as date/time. Seems that PBI will have to pull data, from SQL, 3 times instead of just one. Wondering if this will be effective.
I have the same case but additional complexity. I actually have the Main table A disabled load.
Only referenced table B & C are only loaded but not able to set incremental refresh as both will not have the parameters set.Expertise support needed.
Holy cow, I am dealing with the same problem and thought I'm alone with it
I also opened a thread about that
Furthermore, I set a ticket at Microsoft and we are trying to fix that.. as soon as I got an answer, I will post it here
Regards
