Load data files from folder and Power Query to load each excel sheet from each excel file

you can load all the file by the below code (with comments for each step)

let
    // Set the folder path
    FolderPath = "C:\YourFolderPath\",

    // Load all files from the folder
    Source = Folder.Files(FolderPath),

    // Filter to only include Excel files
    ExcelFiles = Table.SelectRows(Source, each Text.EndsWith([Name], ".xlsx") or Text.EndsWith([Name], ".xls")),

    // Add a column to extract data from each file
    AddCustom = Table.AddColumn(ExcelFiles, "Data", each Excel.Workbook([Content], true)),

    // Expand the Data column to get each sheet from each file
    ExpandSheets = Table.ExpandTableColumn(AddCustom, "Data", {"Name", "Data"}, {"SheetName", "Data"}),

    // If you want to filter by specific sheet names, uncomment and modify this line:
    // FilterSheets = Table.SelectRows(ExpandSheets, each [SheetName] = "Sheet1" or [SheetName] = "Sheet2"),

    // Expand the data in each sheet
    ExpandData = Table.ExpandTableColumn(ExpandSheets, "Data", Table.ColumnNames(ExpandSheets[Data]{0})),

    // Optional: Add a column to identify the source file name
    AddSourceInfo = Table.AddColumn(ExpandData, "SourceFile", each [Name])
in
    AddSourceInfo

If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos. Thank you!