Expand data table only for 1st sheet without specifying sheet name

Hi Anonymous ,
You can directly start with blank query.
You can use this query and modify as per your folder name-
let
Source = Folder.Files("C:\\Your\\Folder\\Path")                          // Change this to your folder path
ExcelFiles = Table.SelectRows(Source, each Text.EndsWith([Extension], ".xlsx") or Text.EndsWith([Extension], ".xls")),
AddFirstSheet = Table.AddColumn(ExcelFiles, "FirstSheet", each try Excel.Workbook([Content], null, true){0}[Data] otherwise null),
RemoveNulls = Table.SelectRows(AddFirstSheet, each [FirstSheet] <> null),
ExpandedData = Table.ExpandTableColumn(RemoveNulls, "FirstSheet", Table.ColumnNames(RemoveNulls{0}[FirstSheet])),
FinalTable = Table.SelectColumns(ExpandedData, {"Name"} & Table.ColumnNames(RemoveNulls{0}[FirstSheet]))
in
FinalTable
This will get you one table with the first sheet from every file
If column names are inconsistent across sheets, consider normalizing them before combining.
Hope this helps!