Forum Discussion
Power Query Combine Multiple Files From Folder
Hi community,
I'm working on combining 90 excel files from a folder.
But the data combined from all files is compiling vertically and I need to change it so I can choose only the columns I need from each file.
Columns are consistent within all files being combined
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
8 Replies
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?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
Hi MarkFlrz as you mention you have 90 excel file and wants to load while keeping only selected columns.
Lets do it like this.
Step 1: Get Data > From Folder -> Click "Transform Data"
Step2: Give a name "data" and keep only Content and Name column
as you can see, i have total 3 excel files.
Now, create a blank query and pest this in advance editor
let Source = data, Combined = Table.ExpandTableColumn( Table.AddColumn(Source, "Data", each Table.PromoteHeaders(Excel.Workbook([Content]){0}[Data])), "Data", {"Invoice No", "PO No", "Customer", "Amount", "Date"}, {"Invoice No", "PO No", "Customer", "Amount", "Date"}), Cleaned = Table.SelectColumns(Combined, {"Invoice No", "Date", "Amount"}) in CleanedResults:
As you can see, in total i have 5 columns but i am selecting only 3 columns. You need to update this column name.
Here is sample https://drive.google.com/drive/folders/1OWAVP7hoqbRNiCgphJuF7s23466vTx9G?usp=drive_link
Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!
We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.
MasonMA & Royel ,Thanks for your prompt response
Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community supportWe would like to confirm if our community members answer resolves your query or if you need further help. If your query still not resolved can you please provide sample data as raisurrahman requested? this will help find solution easily.
Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community supportMay I ask if you have resolved this issue? If so, Can you please share the resolution steps here. This will be helpful for other community members who have similar problems to solve it faster.
If we don’t hear back, we’ll go ahead and close this thread. For any further discussions or questions, please start a new thread in the Microsoft Fabric Community Forum we’ll be happy to assist.
Thank you for being part of the Microsoft Fabric Community.
