Forum Discussion
Incremental refresh - Staging tables
I bet this is common to everyone who is doing incremental refresh in Power BI and I could not find a better solution.
I have two tables (A, B) in my relational warehouse which I import them to the PBI desktop. Then I merge those tables to a new query (C) using LEFT OUTER JOIN removing some columns from A and B that I do not need. All my visualizations will be based on Table C now.
I set up the incremental refresh on all three tables (A,B,C) and pushed it to Service. The questions that I have:
- Can I remove those two staging tables (A, B) (From my research it is NO as they are referenced by the Merged Query). I hid them from the report view but is this the best we could do ?
- Since Table C is now the main table, do I still need to have incremental refresh turned on in A and B ? Can I just do the incremental refresh on C ? If yes, do I have to bring in both date columns from A and B to implement Incremental refresh on C?
I just wanted to have an efficient solution and not just a thing that works.
Hi anilthapa11 ,
check out this blog post.
https://community.powerbi.com/t5/Community-Blog/Incremental-Refresh-with-Subqueries/ba-p/1003337
Hi anilthapa11 ,
- Since Table C is referenced from Table A and Table B, both of them cannot be deleted as your previous said. If you don't want to show them in Fields pane, hide table may be a best way to do this.
- About set incremental refresh on Table C, I think you can just configure incremental refresh on Table C without setting Table A and Table B at the same time bacause Table C is now a single independent table and the parameters RangStart and RangEnd are public to use in the same report in power queries. You should use these two parameters and filter the date column on Table C configure incremental referesh on it and it should work fine. For more information about parameters in incremental inrefresh, please refer the microsoft document: https://docs.microsoft.com/en-us/power-bi/service-premium-incremental-refresh
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi anilthapa11 ,
check out this blog post.
https://community.powerbi.com/t5/Community-Blog/Incremental-Refresh-with-Subqueries/ba-p/1003337
Thanks mwegener
That blog is exactly what I was looking for. However, I could not accurately follow each steps which I asked in the blog itself. Hope I get my answers there.
Hi anilthapa11 ,
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
Hi anilthapa11 ,
- Since Table C is referenced from Table A and Table B, both of them cannot be deleted as your previous said. If you don't want to show them in Fields pane, hide table may be a best way to do this.
- About set incremental refresh on Table C, I think you can just configure incremental refresh on Table C without setting Table A and Table B at the same time bacause Table C is now a single independent table and the parameters RangStart and RangEnd are public to use in the same report in power queries. You should use these two parameters and filter the date column on Table C configure incremental referesh on it and it should work fine. For more information about parameters in incremental inrefresh, please refer the microsoft document: https://docs.microsoft.com/en-us/power-bi/service-premium-incremental-refresh
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
