Merging data from multiple files - Best solution?

[ClementR]

Hello,

I am contructing a report where I need data from multiple excel files (~50). Each file contains multiple dataset, that should be merged across all files. E.g. I want to merge data from Sheet1 from all files, then data from Sheet2 from all files (around 10 different dataset). 

For the moment, I have one query per dataset, which calls one function on each file. So around 10 functions repeated across 50 files. It takes a lot of time to refresh.

To make it quicker, should I look for a way to merge all data from each file (using a list of reports for each table maybe) into a single big dataset, then split this dataset into the 10 tables I need? Could this make the refresh quicker, as each file will be called once by the query?

Thanks in advance,

Clément

[Anonymous]

Hi @ClementR ,

You can use combine file in query editor. Put these three files in one folder and connect to the folder via Power BI. 

For detailed steps , you can follow :Combine files (binaries) in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.