Combining multiple GB of Excel-data from various folders with similar structure

[Toalle]

Hello all!

I am trying to combine hundreds of Excel-files, and each one of them has approximately 100 columns and 10k-100k rows. So the total amount of rows is probably tens of millions.

The folder/file structure is like this (download, password: mrexcel):

https://easyupload.io/l4n5yh

So basically there is a folder for every country, and under every country there is a subfolder for a project. That subfolder then contains standardized folder structure, and further in the folder structure there is a folder called 'Data'. That data-folder contains Excel files which all have identical formatting, and the data is presented in a table which always has the same name, in this case, xdata.

From the data files, I do not need all the columns, only about 10 or so. Same applies to every data file. I would like to place filters in Power BI reporting view, which allows me to select a country and a project, and BI would then load let's say for example 3 latest data files from the folder in question, matching my filter selections.

Any tips on how to approach this without choking BI from all that data? Would be greatly appreciated Thank you!

 

[schwiggiddy]

I don't know that you'd choke PBI with that much. PBI can handle 2 billion or so rows. Sure, it'd be better if it were all stored in a database already, but you work with what you've got.

I've done similar transformations, albeit maybe not quite that large. If you're particularly concerned with the size of the dataset, create a set for each country or groups of countries and duplicate the report on different pages for each set.

Otherwise, before combining the files in PBI, filter the folder location to include "Data" and the file names to include "xdata." After PQ creates the helper queries to combine them, you may need to go back to a remove other columns step and make sure country identifying columns are retained to be used as filter criteria later (unless that is included in the files themselves).

It will take a while to process.

Unfortunately, I don't know of any way to not Select * all the columns out of excel files when loading them up, which is why it'd be better if they were in a db to be pulled out with SQL.

Hope that helps. Good luck.