(M code)How to extract multiple sheet data from Excel which in multiple folder by Power Query(noDAX)

Hi QZ ,
Based on your description, you can try to perform the following steps:
First, choose get data from folder

Then,choose the target folder and open and choose the combine and transfrom data

Click ok

Open the advanced editor and paste the Mcode

let
    Source = Folder.Files("C:\Users\yourcomputername\Desktop\DeptBudget"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Content", "Name"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind", "Custom.Hidden"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Expanded Custom",{"Custom.Name", "Custom.Data"}),
    #"Expanded Custom.Data" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom.Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Custom.Data.Column1", "Custom.Data.Column2", "Custom.Data.Column3", "Custom.Data.Column4", "Custom.Data.Column5", "Custom.Data.Column6"})
in
    #"Expanded Custom.Data"

Final output

Note that you can format all the data before merging it, so that you can get the data into the desired format faster.
How to Merge [Combine] Multiple Excel FILES into ONE WORKBOOK (excelchamps.com)

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly