Tab name change dynamically updated

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.