Forum Discussion

captainlaw's avatar
captainlaw
Microsoft Employee
9 years ago

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 ...
General Comment
MarcelBeug's avatar
MarcelBeug
Community Champion
9 years ago

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:

 

    • captainlaw's avatar
      captainlaw
      Microsoft Employee
      9 years ago

      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?

      • MarcelBeug's avatar
        MarcelBeug
        Community Champion
        9 years ago

        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.