Combine 600 Excel files with different Sheet name

[HSK25]

I receive the production reports every week via email from 10 different sites, each in Excel format. Each Excel file contains multiple sheets, but I am only interested in the "Daily" sheet (or "DKPI" in some sites). This sheet lists 20 common parameters in the rows and dates in the columns. Some sites include more than 20 parameters, but I only need the common 20. Additionally, there are a few blank rows at the top, which vary from report to report. I want to combine all these reports and analyze the past five years of data (approximately 600 Excel files) in Power BI. Could you guide me on the best way to accomplish this task efficiently?

[v-yiruan-msft]

Hi @HSK25,

Before considering how to combine these 600 files, I recommend performing the following data cleaning tasks on the data source side to facilitate loading and analyzing the data in Power BI:

1. Develop Unified Naming Rules for Column Names: Since you need 20 specific columns, please establish a standardized naming convention for these columns. Request that the personnel at each site adhere to these naming rules when preparing the data files. Ensure that they delete any blank rows and include only the 20 required columns. Additionally, ask them to send only the relevant sheets ("Daily" or "DKPI").

2. Verify File Contents: Confirm that all 600 files contain the necessary information. If possible, consolidate the data by site to reduce the number of files. Because it would be a very heavey task to load these 600 Excel files directly in Power BI.

3. Organize Cleaned Data Files: Create a dedicated folder and place all the cleaned data files in this folder.

4. Load and Analyze Data in Power BI: Refer to the link provided by wini_R and danextian for detailed instructions on loading and analyzing the data in Power BI.

Best Regards

[wini_R]

Hi @HSK25,

In addition to danextian's answer, you might also find these videos useful:

1. Dyanmically combine data from multiple Excel files: https://www.youtube.com/watch?v=ktgdDPNXiMg

2. Dynamically remove junk rows: https://www.youtube.com/watch?v=kXBGWqE3TeQ

[HSK25]

I Tried the method as per the videos before posting the question here. the vidos doesnt talk about

1. selecting the right one from the bunch of worksheet in each excel file. the one i am interested in have different names in another file.

2. the column names are rows and the record (Rows are in columns. so we have to transpose them or unpivot them.

3. the column names are different in all file and sheets. actually these are dates.

Do you suggest  a method to combine the data from all 600 excel files.

[danextian]

Hi @HSK25 

600 files and not to mention the number of tabs inside can result to a very slow query. Slow but possible. The performance will depend on the specs of your device and the  ETL to be done. My tutorials on youtube might come handy

https://youtu.be/AzMaf42DmM0

https://youtu.be/zhCWiIwO8dQ

I suggest you start with a few files first and then add

[HSK25]

each file consists of 4-10 sheets (varies sit to site)

[danextian]

The current combine and transform feature in the GUI allows for just  one worksheet because of the need to select a sample sheet.  My YouTube vids shows how you can connect to those sheets. But 600 just for the workbooks alone is a big number.