Forum Discussion

katiejroberts's avatar
katiejroberts
Frequent Visitor
8 years ago
Solved

Repeatedly appending rows from source file to a table without losing old data

Hi there,   Apologies if the title doesn't spell it out clearly. I'm having trouble with appending rows to the bottom of a table.  I have an Excel file that contains 2017 survey data (call it file ...
  • katiejroberts's avatar
    katiejroberts
    7 years ago

    I ended up finding a sort of solution for this issue.

     

    I've adjusted my process so it saves a new weekly data file (only containing the last 7 days of data) in the same folder every week, and have my pbix connected to that folder.  So when a new file has been added to the folder, that is reflected in my .pbix.  

     

    It's not perfect - the weekly export is coming from the internet and I get a OLE DB or ODBC Error: [DataFormat.Error] External table is not in the expected format... error when I refresh sometimes.  I suspect maybe there's an invisible file maybe coming in from my download from the internet?  I'm not sure.  If I get that error and then move all my files to a folder of the same name and delete the original folder, then refresh, it works without issue.  So that's a separate little problem.  But for now, this is sort of taking care of my issue. 

     

    Thanks everyone. 

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi katiejroberts,

 

Currently, the Power Query will override the old data and only show the new data after refreshing. Incremental data load is not yet supported now. But it would be released in the feature as mentioned here.

 

As a workaround, if possible, you could store historical data in your source system where your data comes from. Or find a way to maintain all historical files locally. Each time you refresh data will generate a new file. Then, load data in each period from those files into desktop.

 

Best regards,

Yuliana Gu