How to merge multiple workbooks with multiple sheets in a SharePoint folder

[chuckbo]

I'm trying to merge a bunch of workbooks in a SharePoint in a folder but keep all of the worksheets separate when they merge across files.

What I mean:
Imagine I have a State file Alabama.xlsx that contains 3 tabs
May Sales
June Sales
July Sales
All of the tabs have five columns: Date, Customer, Product, Quantity, and Total Receipts

And I have a workbook for each state.

I want to create a Power Query that will merge the 50 May sheets into a single sales sheet, merge the 50 June sheets into a single sheets, and merge the 50 July sheets into a third sheet. I built the query for the May table without problem. Do I have to start over to define the 2nd and 3rd table? I was looking for a way to copy/paste the first query and then just change the May sheet to the June sheet, and I have two copies of the May table. I haven't been able to find the step in the query that specifies the sheet (and change it from May to June).

Chuck

 

[Matt Allington]

Why can’t you combine all together? It would be a lot easier (see link). If you need to do it, then create a parameter to set the month name, save a copy of the master file and then change the parameter.

Combining Multiple Sheets from Multiple Workbooks

This article shows how to combine both sheets and workbooks together in Power Query using some tricks and advanced M language functions

exceleratorbi.com.au

 

[chuckbo]

You are correct.

However, I simplified the example to make it more understandable. There are a lot of peculiarities that prevent us from merging the actual sheets.
Chuck