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

[AqiffM]

Hi All,

I have data source which is from folder, I choose the directory and I combine and transform data (at this stage, i can only choose 1 sheet)

Different excel sometimes have 2 excel sheets and 3 excel sheets. but the sheet names are consistent

[Omid_Motamedise · Accepted solution]

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!

[AqiffM]

where do i put this code? i put in power query it gives me as below ...

[Omid_Motamedise]

Go to the power query, then in the Home tab, pick Advance EDitor, in the oppened window, remove all the command and past the code I have provided.

in the code I have mention 

C:\YourFolderPath\

as folder path, replace it by your own folder path

[AqiffM]

this helps a lot! any recommendation where i can learn more on advance editor?