Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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
Proud to be a Datanaut!
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.
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
Proud to be a Datanaut!
Check out the July 2025 Power BI update to learn about new features.