Tab name change dynamically updated

[fbittencourt]

Hi All !!

Everytime that my excel file tab names changes I need to update manually, is there way to update automatically on power query?

Like if my tab changes name from 3 to 4

Sample pbi :

let
Source = Excel.Workbook(File.Contents("C:\Users\h22012\OneDrive - BNP Paribas\Customers.xlsx"), null, true),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "1" or [Name] = "2" or [Name] = "3")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Data"}),
#"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", "Data.Column8", "Data.Column9", "Data.Column10", "Data.Column11", "Data.Column12", "Data.Column13", "Data.Column14", "Data.Column15", "Data.Column16", "Data.Column17"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Data",{"Data.Column9", "Data.Column10", "Data.Column11", "Data.Column12", "Data.Column13", "Data.Column14", "Data.Column15", "Data.Column16", "Data.Column17"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"1", "Tab name"}})
in
#"Renamed Columns"

Tks in advance!!!

[Zanqueta · Accepted solution]

Hi @fbittencourt.

Yes, Power Query allows you to automatically load all sheets from an Excel file, even if you don’t know their names in advance.

🛠️ Power Query code to load all sheets

let
Source = Excel.Workbook(File.Contents("C:\Users\h22012\OneDrive - BNP Paribas\Customers.xlsx"), null, true),

// Keep only sheets (ignore named ranges and tables)
OnlySheets = Table.SelectRows(Source, each [Kind] = "Sheet"),

// Select relevant columns
SelectedSheets = Table.SelectColumns(OnlySheets, {"Name", "Data"}),

// Promote headers and combine all sheets
CombinedData = Table.Combine(
List.Transform(SelectedSheets[Data], each Table.PromoteHeaders(_))
)
in
CombinedData
📌 Explanation
Excel.Workbook(...): loads all elements from the Excel file (sheets, tables, named ranges).
Table.SelectRows(..., each [Kind] = "Sheet"): filters only the sheets.
List.Transform(..., each Table.PromoteHeaders(_)): promotes headers for each sheet.
Table.Combine(...): merges all sheets into a single table.
🔁 Optional: Keep sheet name as a column
If you want to track which sheet each row came from, you can add the sheet name before combining:

SheetsWithName = List.Transform(OnlySheets, each Table.AddColumn(Table.PromoteHeaders([Data]), "SheetName", each [Name]))
CombinedData = Table.Combine(SheetsWithName)
⚠️ Important Notes
All sheets should have the same structure (same columns), otherwise Table.Combine may produce errors or nulls.
If structures vary, you may need to standardize them before combining.
✅ If this solved your issue, please mark it as the accepted answer to help others in the community.

[fbittencourt]

Sorry to bother you, if you can help me I appreciate ,but I have an error when I add to keep the sheet name

Tks!!

[lbendlin]

You can select rows by index rather than by a name search.  

Ideally your data source should not change in the way you describe.

[fbittencourt]

I agree with you, but the tabs are related to periods ( last 6 months) , after the update it changes the name like ( last 9 months) , so pbi do not recognise automatically...

[lbendlin]

Define a way to sort the tab names descending and then grab the first item {0}

[parry2k]

@fbittencourt is there always a one sheet in the Excel file?

[fbittencourt]

Hi Parry,

We have only one excel file with many period tabs, the person update the tabs concerning the period of the analysis, like tab last 6 months after update changes the name last 9 months, and power bi do not recognise, the option is to change on advance editor or import again with the new name, I need to know if there´s a way to change automatically, maybe on power automate. tks again!!