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 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
Solved! Go to Solution.
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 _ Janey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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:
@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
Hi @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
@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
@amitchandak My datasource is a folder, not a single file:
let
Source = Folder.Files("C:\Users\.....\PowerBIData\data sources\moves_adjusts"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File (2)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"TransactionDate", type date}, {"TransactionTime", type time}, {"TransactionType", type text}, {"Tra Text 1", type any}, {"ItemCode", type text}, {"ProductCode", type text}, {"NumberOf", Int64.Type}, {"LocaCodeFrom", type text}, {"LocGroupCodeFrom", type text}, {"LocaCodeTo", type text}, {"LocGroupCodeTo", type text}, {"Operatorcode", type text}}),
Hi, @MichaelF1
What do you mean by name of the actual tab?
Not sure what your needs are, Please add more details so we can help you soon.
Best Regards,
Community Support Team _ Janey
Hi Janey, thanks for getting back to me.
I mean the name of the actual sheet in excel. (see picture)
Thanks again
Michael
Hi, @MichaelF1
When you click on the content of one specific xlsx, you will see the corresponding sheet name or table name. And you can change them. Please note that what you change is only the display in powerbi, powerbi won't change the content of the source data.
Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,
Community Support Team _ Janey
Hi @v-janeyg-msft , thanks for your suggestion but unfortuantely this does not give me the result I need. If I click the Binary hyperlink as in your suggestion, I get the dialogue box below:
If I click continue, then all my subsequent steps in power query are erased - so I have to start my query almost from scratch gain. I definitely don't want this!
What I want is just to be able to change the name of the sheet that power query looks for, but keeps everything else in the query as it is.
Thanks again,
Michael
Hi, @MichaelF1
I don't understand your question very well, as I mentioned before, it is impossible for powerbi to modify anything in the source data, it can only modify the content displayed in powerquery or desktop.
But I don't see the table name appearing in your steps. Why do you want to modify it? What is the role of what you are trying to modify in pq?
Best Regards,
Community Support Team _ Janey
Hi @v-janeyg-msft ,
Sorry if I wasn't clear, I think I've confused myself! Let me start again and ask my question in a different way:
I have a folder of multiple xlsx files as a data source for PBI - (all the same format, but with data for different months). If I want to change one or more of these xlsx files at a later date, do I need to ensure that the sheet names remain the same, for my Power Query code to work?
Thanks again
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 _ Janey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
71 | |
70 | |
38 | |
26 | |
26 |
User | Count |
---|---|
99 | |
87 | |
45 | |
43 | |
35 |