Distinct Lists from Incrementally Refreshed Table

[BenjaminC]

Hi all, 

I have a large fact table (say, orders) that I'm refreshing via incremental refresh. I love the feature, and it works great. 

My issue is with the steps I perform after that incremental refresh. I'm using List.Distinct to reference a key (say, customers) in that Orders table to create a unique list of customers. I then merge that unique list with my Customers dimension table to only keep those Customers in my dimension table that actually exist in my Orders table. 

From the desktop, this works great, but I run into problems once I publish this to the service. It publishes fine, but after the initial full refresh I notice that my Customers table does not actually get refreshed - it only contains the customers that were included in the subset of the Orders table I used in Power BI Desktop. 

This is the case for any historical Orders data that is added, as well as for any new data that is added.

Has anyone encountered this issue before, or does anyone have any idea how I can resolve this? 

Thanks!

[v-juanli-msft]

Hi @BenjaminC 

Do you use the license premium and use the feature incremental refresh?

Or do you use some workaround with pro license?

Besides, there are some workarounds with a pro license.

https://www.youtube.com/watch?v=u7SyqTpLIJc

https://www.thebiccountant.com/2016/02/09/how-to-create-a-load-history-or-load-log-in-power-query-or...

http://excel-inside.pro/blog/2019/03/21/classical-incremental-refresh-for-cloud-data-sources-in-powe...

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BenjaminC]

Hi @v-juanli-msft, 

Thank you for your reply. Yes, I do have access to a premium workspace, where I do the incremental refresh. So no, I'm not using any workarounds. 

My issue is also not with the incremental refresh. That refreshes the fact table the way I want it to. 

My issue is with the dimension tables I create that reference the incrementally refreshed fact table. They are subsets of the actual dimension tables, that I create by referencing the incrementally refreshed fact table key column(s) in a List.Distinct expression. I then merge that distinct list of keys back to the dimension table. 

My expectation was that Power Query would overwrite the parameters to do the incremental refresh, create the list of unique keys within those overwritten parameters, and merge that back to the dimension table. But after publishing to the service, I notice that it's only actually creating a subset using my original parameters (that I defined in Power BI desktop). So it either keeps using those pre-defined parameters for some reason, or it's not refreshing at all. 

I thought it might have something to do with the parallel loading of queries, but turning that off doesn't change anything.

I hope that makes sense...

Benjamin