Import Multiple Excel Files from a Folder in Power Query M

Reads every .xlsx file in a folder and combines a named sheet from each into one table.

File HandlingAdvancedfolderexcelcombine files

Power Query M code

let
    Source = Folder.Files("C:\Data\SalesFiles"),
    FilteredExcelFiles = Table.SelectRows(Source, each [Extension] = ".xlsx"),
    AddedData = Table.AddColumn(FilteredExcelFiles, "Data", each Excel.Workbook([Content], true)),
    ExpandedData = Table.ExpandTableColumn(AddedData, "Data", {"Name", "Data"}, {"SheetName", "SheetData"}),
    FilteredSheet = Table.SelectRows(ExpandedData, each [SheetName] = "Sheet1"),
    ExpandedSheetData = Table.ExpandTableColumn(FilteredSheet, "SheetData", {"Column1", "Column2"}, {"Column1", "Column2"})
in
    ExpandedSheetData

Notes

Replace the folder path, sheet name and column list with your own. All files must share the same sheet name and layout.

Requirements

A folder containing Excel files with a consistent layout.

How to use this snippet

In Power BI Desktop, choose Transform data to open Power Query, then open the Advanced Editor on a query (or start from a blank query).
Paste the snippet and replace the placeholder names (tables, columns, file paths) with your own. Check the requirements above first.
Run it through the Power Query M Formatter to tidy the layout.

Related Power Query M snippets

More Power Query M resources

Browse all 145 snippets in the Power Query M Code Snippets, or search the library for something similar to Import Multiple Excel Files from a Folder.