Forum Discussion
Power Query Combine Multiple Files From Folder
Hi,
No, you don’t need to add every file name.
You either keep only columns once in the combined query (applies to all files). Or, if need per-file logic filter on Source.Name patterns instead of listing every single file.Also instead of “Combine & Transform” you can also manually control it with a few lines of sample M code like below
let // Step 1: Load all files in the folder Source = Folder.Files("C:\YourFolderPath"), // Step 2: Filter only Excel files (if needed) ExcelFiles = Table.SelectRows(Source, each [Extension] = ".xlsx"), // Step 3: Extract tables/sheets from each file GetTables = List.Transform(ExcelFiles[Content], each Excel.Workbook(_, true)), // Step 4: Append everything together Combined = Table.Combine(GetTables), // Step 5: Keep only the columns you want Final = Table.SelectColumns(Combined, {"AddressID", "AddressLine1", "AddressLine2"}) in Final
Super User
Hi, if all files share schema (same set of columns), then when you “Combine & Transform” in Power Query, you actually don’t get FileA (for example) and FileB separately anymore. You get one appended query with all columns.
If you need to choose columns from each individual files, i'd suggest creating Query Reference.
In the Reference Query, select your File name and Columns you need
and you will see the M code UI generated below, you can further automate this by replacing the hard-coded names or even wrapping it into a function if you want.
let
Source = Data,
#"Filtered Rows" = Table.SelectRows(Source, each ([Source.Name] = "SalesLTAddress.csv")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"AddressID", "AddressLine1", "AddressLine2"})
in
#"Removed Other Columns"
With this option I would need to add each one of the file names?
If so is there a code that I can use to pull all files contained in the folder source?- MasonMA
Hi,
No, you don’t need to add every file name.
You either keep only columns once in the combined query (applies to all files). Or, if need per-file logic filter on Source.Name patterns instead of listing every single file.Also instead of “Combine & Transform” you can also manually control it with a few lines of sample M code like below
let // Step 1: Load all files in the folder Source = Folder.Files("C:\YourFolderPath"), // Step 2: Filter only Excel files (if needed) ExcelFiles = Table.SelectRows(Source, each [Extension] = ".xlsx"), // Step 3: Extract tables/sheets from each file GetTables = List.Transform(ExcelFiles[Content], each Excel.Workbook(_, true)), // Step 4: Append everything together Combined = Table.Combine(GetTables), // Step 5: Keep only the columns you want Final = Table.SelectColumns(Combined, {"AddressID", "AddressLine1", "AddressLine2"}) in Final 
raisurrahmanHelper II
Could you please share some sample data along with the required format as a snapshot? This would be helpful for everyone.
