Getting Data from SharePoint folder and combining multiple sheets in excel

[chinaik123]

Hi,

I need help to combine multiple sheets in multiple excel in a SharePoint folder.
I tried to do so but i can only use the first sheet as Example.

Thanks in advance!

[Omid_Motamedise]

Use Excel.Workbook function to extract data from all the sheets

[shafiz_p · Accepted solution]

Hi @chinaik123  To combine multiple sheets of multiple excel of a folder from sharepoint, follow the steps mentioned below:

Consider each sheets of each excel file have same number of column and same header.

Let's see my demo excel sheets and files. 

Folder name "Test" and multiple type of files. Let's check data and table in each sheet. 

For demo, I have use same table meaning format same in each sheets of all 3 files but have different data. 

Go to Power BI > Get Data > Sharepoint Folder connector. Put the site URL in the box and authenticate. See image:

You will get below window and hit Transform button. See image below:

Power query will open and you will get all the files and folder of you sharepoint site. Go column named "Folder Path" and filter you desired folder only. See image below:

After that you will get your desired files of the selected forlder. Go to extension column and filter only extension .xlsx and then click on double arrow head binary column. See image below:

After clicking double arrow, you will get below screen. Click on folder icon and then ok. See image:

See, all my files and tables are listed. Now i am interested only on tables from all sheet of all excel file. You could choose sheets also. Both are same. See image:

Use dynamic filter to filter name. Because hardcoded will not work if in future you added more sheets and more table. See image below:
 

Since my all table names starts with Tab, I will use that. See image below:

See all the files and desired all tables are in line now. Remove all columns except data. Click and expand data tables and you get all the sheets from all files combined. See images below:

Here is the desired output:

Hope this helps!!

If this solved your problem, please accept it as a solution!!

Best Regards,
Shahariar Hafiz

[chinaik123]

Hi @shafiz_p ,

Thank you so much, this works like wonders. Have been scratching my head for days over this

[SamWiseOwl]

Great solution!

[SamWiseOwl]

Hi @chinaik123 

You can create a function that loops over all the sheets.

This is a video on creating a function to loop over web pages but the principal is the same:
https://www.youtube.com/watch?v=-jc68Mn_k2w

When you connect to the Excel sheet delete all the steps but source

You can then use this list as the items to loop over.

On the navigation step you change the name of the sheet to the parameter you create as mentioned in the video.

Have a go and let me know how you get on!