none
Can I append just the latest files from a folder source? RRS feed

  • Question

  • Hi,

    Using excel 2010 I have created a "Get from Folder" Power Query. The files (.xlsx) in the folder are now growing passed 12000 files. This is now effecting performance as the query is taking around 30mins to load. I need to report on all the files as together they create a historical view of data. Is there anything I can do to improve performance? For example, can I have a preloaded query with the bulk of the data from the files and then append data on a daily basis ontop of this to report against?

    Thanks.

    Wednesday, August 16, 2017 8:34 PM

Answers

  • I can confirm that csv load much faster than xlsx.

    With such a large number of single files, I'd probably choose a different path: Use DAX Studio once a week (or month) to consolidate all your existing files and export them to 1 (!) csv-file: http://biinsight.com/exporting-data-from-power-bi-desktop-to-excel-and-csv-part-1-copy-paste-and-dax-studio-methods/ (this articles shows how it works in PBI, but it is applicable to Excel as well: After you've installed it and opened it from within Excel, you automatically be connected with your PowerPivot datamodel in Excel)

    Even a full load of that large file plus an append of the files that came after the consolidation should improve the query-speed heavily!

    Assuming here that the resulting data exceeds the Excel-row limit - otherwise you just run 2 workbooks: One with a table consolidated data and the other just appending the new data.

    The large number of different files and the fact that they are complex xlsx instead of simple csv or txt is what makes your current process slow.

    Using this method, you could even apply all the PQ-transformations before the bulk-export. So you import a large amount of clean data and apply your PQ-cleaning-transformation only on the new data before appending them all together.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!


    Sunday, August 20, 2017 6:29 AM
    Moderator

All replies