Forum Discussion
Helper IIIHow to edit sheet name in Excel doc when source is a folder
Hi everyone,
I have a PBI report that uses a folder of excel docs as the data source.
I want to change the excel docs and I think I need to change the name of the actual tab in the excel file within power query editor. However, I can't see where the name of the tab is referred to in my M code.
Can anyone give some help with this please?
Cheers,
Michael
Hi, MichaelF1
I probably understand what you mean, but you may need to change your thought and try to use dynamic data sources by using different dates in table names.
You can refer to it first:
Solved: Dynamic Excel name handling in Power BI - Microsoft Power BI Community
Solved: Re: 2 Parameters - Microsoft Power BI Community
Then if you have any specific questions, please feel free to ask me.
Best Regards,
Community Support Team _ JaneyIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
13 Replies
- amitchandak
Super User
MichaelF1 , Not very clear. You can Open Advance Editor, right click on Table in power query and open it
There you can change. You can also use edit Datasource setting
Check how to change connection details
https://www.youtube.com/watch?v=59PUFuuCrbY&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L&index=1
MichaelF1Hi amitchandak , thanks for the reply.
Your video shows how to change the data source if you change the name of the excel file itself, but I want to edit the name of the actual tab in the file. Hope this makes sense, I'll attach the error message.
Thanks again,
Michael
- amitchandak
MichaelF1 , Change sheet name in Item
Source = Excel.Workbook(File.Contents("C:\Users\Amit Chandak\Downloads\PivotData (1).xlsx"), null, true),
Pivot_Sheet = Source{[Item="Pivot",Kind="Sheet"]}[Data],Pivot is my sheet name
Hi MichaelF1
It's been a long time since you queried this issue but I've experienced the same issue and couldn't find any satisfying solution in forums until I consulted ChatGPT 🙂 I wanted to change the sheet name in Excel from "Billing details_C2S2" to "Billing details". Here's it's answer that perfectly solved my issue:
From the M code you provided, it looks like the sheet name is not explicitly referenced because you are using a folder query (Folder.Files), which transforms multiple files in the folder, and the transformation is done via a custom function (Transform File (11)).
The problem likely stems from the step where this custom function is called. Since you renamed the sheet inside the Excel file, the function "Transform File (11)" might still expect the original sheet name, "Billing details_C2S2", leading to the error.
Here’s what you can do to fix it:
Steps to Edit the Custom Function:
- In Power Query Editor, go to the Queries pane on the left and locate the query named "Transform File (11)" (this is the custom function).
- Click on this function to view its steps.
- Inside this function, find where it refers to the sheet name "Billing details_C2S2".
- Update this reference to "Billing details" (the new sheet name).
- Apply the changes and re-run the query.
