Incrementally load new data without processing all dataset

[mribeiro]

Hi guys,

I'm with a problem to update "new" data to a table with incremental refresh configuration.

Follow bellow a summary of the model.

I have a referense table conected to others, where I have the the App Keys and Secrets of my clients (The tables dProjects and fSales come from a web datasource).

When I add a new client, in the next scheduled refresh on Power BI Service, automatically the tables will update with this new data. But, the problem is with fSales. How the incremental refresh is configurated, the data from this new client don't will be loaded in the past.

Of course, I can force the update using Tabular Editor or SSMS, but I don't want to update all my dataset fSales, because I have to many clients and it can take to many hours.

So, I would like to run a refresh script with a where contitional in a column (Refresh where dCllients[Client] = "New client"), is this possible? Or what's the best solution in this case?

Thanks in advance.

[collinq]

HI @mribeiro ,

As I am sure you know, the incremental refresh is using a date column to determine the "incremental" data to refresh and it is looking for changes in data since that date.  Based on that date parameter the system will not go back in time to find previous sales transactions for that specific client since that would be "archived data" already and not picked up.

If I understand your description this is exactly what is happening.

I don't think that there is a particularly easy programmatic way to get around this issue.  If this is something that happens infrequently the way I would do this is to create a new query - similar to yours - where you go and JUST get the new client data, then, I would append this to the master sales table and that way you have the historical data available to you and going forward the new client is picked up with the incremental refresh.

Let me know your thoughts.

[mribeiro]

Hello @collinq ,

Thanks for your reply!

Exactly.. you got the issue.

You had a great idea. I followed your suggestion, but unffortunelly I came cross other issue: It's not possible append data in a already incremental configurated table. It's simillar of this issue: https://community.powerbi.com/t5/Service/Incremental-refresh-from-Azure-DataLake/m-p/559711#M56914

You have some suggestion to get around this new issue?

Thanks.

[collinq]

HI @mribeiro ,

Hmmmmmm, ok, perhaps you could do part (or all) of this in a DataFlow?  You could have the "old" stuff in the dataflow and not necessarily refresh it every day?  OR, you could do the new stuff in the dataflow?  Either way, if half the work is done in a dataflow and then you just combine the data together in the final report I think that should work.