Expand data table only for 1st sheet without specifying sheet name_Re-post

Hi Anonymous ,
Thank you for the follow-up.I would be happy to assist you!

Please use the below in transfrom data ->advance editor then close and apply.

let
// Replace this with your folder path
Source = Folder.Files("C:\Users\v-pagayam\OneDrive - Microsoft\New folder"),


ExcelFiles = Table.SelectRows(Source, each Text.EndsWith([Extension], ".xlsx") or Text.EndsWith([Extension], ".xls")),


AddFirstSheet = Table.AddColumn(ExcelFiles, "FirstSheet", each 
let
wb = Excel.Workbook([Content], null, true),
visibleSheets = Table.SelectRows(wb, each [Kind] = "Sheet" and [Hidden]? <> true),
firstSheet = if Table.RowCount(visibleSheets) > 0 then visibleSheets{0}[Data] else null
in
firstSheet),


RemoveNulls = Table.SelectRows(AddFirstSheet, each [FirstSheet] <> null),


Expanded = Table.ExpandTableColumn(RemoveNulls, "FirstSheet", Table.ColumnNames(RemoveNulls{0}[FirstSheet])),


FinalRaw = Table.SelectColumns(Expanded, {"Name"} & Table.ColumnNames(RemoveNulls{0}[FirstSheet])),


Cleaned = Table.SelectRows(FinalRaw, each [Column1] <> "Sales" and [Column2] <> "Quantity")
in
Cleaned

Rename the columns as per the requirement. Please refer the screenshot and file for detailed understanding.

If this answer meet your requirement,consider accepting it as solution.If still facing difficulty in resolving the issue, feel free to reachout us!

Regards,
Pallavi.