cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
captainlaw Member
Member

Refresh Data - Append New Data Only

Hello,

I'm wondering is it possible to setup refresh of a data source based on date column, meaning only append new data from last load based on date column. 

It would be a good feature to add for PBI in the future.

7 REPLIES 7
Super User
Super User

Re: Refresh Data - Append New Data Only

Sorry, This feature not yet implemented in Power BI.

 

I think they are working on it. 

 

If u r facing this prob i personally suggest u go with SSAS Tabular cube Live connection that will help u

Super User
Super User

Re: Refresh Data - Append New Data Only

A workaround - maybe strange, questionable and controversial, but still a wordkaround - would be to fix your data by copying and pasting into "PreviousData" and then modify your query to have new data appended to the previous data.

After each refresh, you need to copy and paste into PreviousData.

I guess it would be best to use that dataset as the source for your reports (rather than the new dataset), so you won't forget to copy the data to PreviousData.

Edit: it is assumed that "Date" is the primary key. Adjust as appropriate, 

 

let
    Source = Csv.Document(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Append new Data only\Inputdata.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source),
    NewData = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Value", Int64.Type}}),
    Merged = Table.NestedJoin(PreviousData,{"Date"},NewData,{"Date"},"NewColumn",JoinKind.RightAnti),
    Combined = if Table.IsEmpty(Merged) then PreviousData else Table.Combine({PreviousData, Merged[NewColumn]{0}})
in
    Combined

 

 

Steps are illustrated in this video:

 

Specializing in Power Query Formula Language (M)
Super User
Super User

Re: Refresh Data - Append New Data Only

Nice workaround Marcel!

@captainlaw @Baskar @MarcelBeug: Please vote for this feature here: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/7288623-incremental-data-loads

The more votes, the quicker it will be implemented!

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




captainlaw Member
Member

Re: Refresh Data - Append New Data Only

In the workaround, assuming here's what we've loaded in the report -

Current dataset 11/1-11/30

Previous dataset 10/1-10/31

 

From the video, so the step is to copy Current and overwrite Previous.

Previous dataset 11/1-11/30

 

Then, we load latest dataset -

Current dataset 12/1-12/15

 

So base on the script, it will refresh Previous + Current = 11/1-12/15

Am I correct in the above assumption?

If so, what happened to dataset 10/1-10/31?  I'm looking for a solution that will keep all data, but only refresh/append new data.

For my scenario to work, can we still only have 2 datasets or do we need to continue to add more?

Super User
Super User

Re: Refresh Data - Append New Data Only

Please note that the workaround will not be suitable for large data sets.

 

Your assumptions are not correct as new data will be appended to PreviousData each time in Dataset, so Dataset will have all data after refresh.

 

In your examples:

In CSV 11/1 - 11/30.

In PreviousData 10/1 - 10/31.

 

After refresh, Dataset will have data 10/1 - 11/30.

Copy this to PreviousData.

 

New CSV file 12/1 - 12/15.

After refresh, Dataset will have data 10/1 - 12/15.

Copy this to PreviousData.

 

So you'll only have 2 datasets.

Specializing in Power Query Formula Language (M)
Pankaj1208 Visitor
Visitor

Re: Refresh Data - Append New Data Only

Hi.... Its good but when i replace the value second time then its not update in previous table so please give the solution 

powerbiuser101 Regular Visitor
Regular Visitor

Re: Refresh Data - Append New Data Only

This doesn't work for me either. My dataset does not update on the 2nd refresh. This is rather confusing as I had thought the idea was to append data to "Previousdata" from an ever changing "Dataset"? Based on MarcelBeug's reply, sounds like this was the opposite. I don't understand the "Copy this to PreviousData" step. Shouldn't the Merged/Combined step be added to "Previousdata" rather than "Dataset"?

 

 

 

 

Merged = Table.NestedJoin(PreviousData,{"Date"},NewData,{"Date"},"NewColumn",JoinKind.RightAnti),
    Combined = if Table.IsEmpty(Merged) then PreviousData else Table.Combine({PreviousData, Merged[NewColumn]{0}})
in
    Combined

In my testing, Combined was also referencing an older Table in Merged[NewColumn]. Which made sense to me why it worked the first time and not any subsequent refresh. Re-adding 2 new additional steps as above worked but this would cause some step-bloat if this has to be done every single time I need to append new data.