Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
UncleLewis
Responsive Resident
Responsive Resident

Get Data From Folder - Same Sheet Name Question

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

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

2 REPLIES 2
v-jingzhang
Community Support
Community Support

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]

vjingzhang_0-1657681562291.png

 

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]

vjingzhang_1-1657684086030.png

 

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

BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors
Top Kudoed Authors