How to load & combine data from multiple sheets in multiple files in a SharePoint folder

[chuckbo]

I'm trying to add a new table to a PowerBI report. We want to load from all of the Excel files that are in a SharePoint directory. Each of the Excel files has multiple sheets. We want to combine two of the sheets from all of the files into a new table.

I haven't used PQ from Power BI before; all of my past experience is drawing from databases. But I've been using PQ recently in Excel, and this seems like something I should be able to do but haven't found the right sequence yet.

1) I'm getting data from a SharePoint folder and specifying the path.

2) I'm filtering the column that lists all of the files to the path that has my files.

3) I expanded the Binary content and get the Combine Files dialog box. I see four sheets in the sample file, and I'm interested in two of them. These will be the same two sheets that I want to use in all of the files. But I'm only able to select one of them at a time and I'm stuck.

[chuckbo · Accepted solution]

I got some help here in our office and wanted to circle back and update my question here with the answer.

1) I selected SharePoint folder as the data source and entered the link.

2) from the list of files in the site, I selected Transform Data.

3) I expanded the Binary column to see the sample file and the sheets in the file.

4) I selected the first sheet to load from the sample file and pressed OK.

5) I switched to the power query window and changed the query name.

6) in the Applied Steps pane, I deleted everything after the Invoke Custom Function1 step.

6) the last column in the table has a title of Transform File, and I expanded it.

 7) on the left hand pane, under Helper Queries, select the Transform Sample File query.

😎 in the applied steps, select the Filtered Rows step and select the sheets to include to merge.

that concludes the steps to select multiple sheets from multiple files in a directory.

9) then, you can select the query that you're building in the left hand pane again and add any other transformations (such as expanding Transform file to select which fields to include).

[chuckbo]

Thanks for the link. I've seen this one. Im not sure it will help, but I'll try it again.

This requires the data to be in tables or in named ranges, which they arent't. I could easily write some code to loop through all of the files in a directory and convert all of the sheets to tables each time I have to do a refresh, but that kind of subverts PQ. 
also, the video describes how to combine multiple tables within a single file but not how to expand that to multiple files within a SharePoint directory.

let me place with this -- see if I can figure out some workarounds.

[ND_Pard]

Hi,

Watch the video at the following website, I think it will provide you with ideas on how to resolve your question.

https://trumpexcel.com/combine-multiple-worksheets/

Good Luck!