Get Data From Folder - Same Sheet Name Question

[UncleLewis]

Hi,

Im using Power Query in Excel.
Is it possible to Get Data >> From File >> From Folder when the the sheet in each file has different names?
I keep trying, but I don't get the return I expect

Here's the layout
FileName | SheetName
--------------------------
Region1 | R1
Region2 | R2
Region3 | R3

etc....

As a test I setup new files with some data and saved to a new folder. On these files, I left the sheet name as Sheet1. On the test files, I was able to import all data exactly as I expected, so it seems the sheet name must be exactly the same in ever workbook?

Thanks,

-w

[v-jingzhang]

Hi @UncleLewis 

It is because the sheet name is hardcoded with the sheet name from the sample file. You can try @BA_Pete 's solution if you have only one sheet in each file. You only need to modify the Navigation step in Transform Sample File query. 

Modify

Source{[Item="R1", Kind="Sheet"]}[Data]

into

Source{0}[Data]

If you have more than one sheets in each file and you only want to import data from the sheet whose name starts with "R", you can modify the Navigation step into 

= Table.SelectRows(Source, each Text.StartsWith([Name], "R")){0}[Data]

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[BA_Pete · Accepted solution]

Hi @UncleLewis ,

If you only have one sheet in each file, but they have different names, then you can replace the sheet navigation step like this:

// change your step that looks like this:
Sheet = Folder{[Name = "Sheet Name"]}[Content]

// to this:
Sheet = Folder{0}[Content]

// or this, depending on how you are importing the workbook:
Sheet = Folder{0}[Data]

You're basically just switching the hard-coded sheet name for any sheet that is at position 0 (the first sheet in a zero-base list) in the workbook.

Pete