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 PBI in the future.

7 Replies

  • MarcelBeug's avatar
    MarcelBeug
    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:

     

      • captainlaw's avatar
        captainlaw
        Microsoft Employee

        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?

  • Baskar's avatar
    Baskar
    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