Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
captainlaw
Employee
Employee

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
MarcelBeug
Community Champion
Community Champion

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)

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!

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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?

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)

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

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. 

Baskar
Resident Rockstar
Resident Rockstar

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.