Forum Discussion
Combine all sheets with different names from multiple files
Hi,
I have multiple cash book files representing different companies, each file has multiple worksheets, and each worksheet has a different name to represent a different bank account.
All worksheets have the same column headers.
Is it possible to merge all these worksheets with different names in all files into one table?
I can only find solutions to merge multiple worksheets from a single file or merge multiple worksheets with the same name from multiple files, but these do not solve my problem.
Anyone can share the solution?
Hello Anonymous
read from a folder, where you have all your files. On the folder data add this formula (hopefully you are reading from a sheet and not from a table, otherwise you would need to adapt the code a little bit
Table.Combine( List.Transform( Excel.Workbook([Content])[Data], each Table.PromoteHeaders(_)))this formula gives you combines tables form every single file. Right click on your step on the right side and choose insert step after. User here now again a table.combine where you are reference your previous step and your newly created column
Table.Combine(#"Added Custom"[TablesFromFile])here a complete example
let Source = Folder.Files("YourFolderWithFilesToMerge"), #"Added Custom" = Table.AddColumn(Source, "TablesFromFile", each Table.Combine( List.Transform( Excel.Workbook([Content])[Data], each Table.PromoteHeaders(_)))), CombineAllFiles = Table.Combine(#"Added Custom"[TablesFromFile]) in CombineAllFilesCopy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyHello Anonymous
this code now adds the sheet name before combining
let Source = Folder.Files("YourFolderWithFilesToMerge"), #"Added Custom" = Table.AddColumn(Source, "TablesFromFile", each Table.Combine(Table.AddColumn(Excel.Workbook([Content]), "WithSheetName", (add)=> Table.AddColumn(Table.PromoteHeaders(add[Data]),"Sheet name", each add[Name] ))[WithSheetName])), CombineAllFiles = Table.Combine(#"Added Custom"[TablesFromFile]) in CombineAllFilesCopy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
8 Replies
- AlB
Community Champion
Hi Anonymous
If you can already merge multiple worksheets from a single file, what exactly is the problem? You can just merge the queries that result from those files, can you not?
It would help if you can share a folder with some dummy files that reproduce the problem
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Jimmy801Hello Anonymous
read from a folder, where you have all your files. On the folder data add this formula (hopefully you are reading from a sheet and not from a table, otherwise you would need to adapt the code a little bit
Table.Combine( List.Transform( Excel.Workbook([Content])[Data], each Table.PromoteHeaders(_)))this formula gives you combines tables form every single file. Right click on your step on the right side and choose insert step after. User here now again a table.combine where you are reference your previous step and your newly created column
Table.Combine(#"Added Custom"[TablesFromFile])here a complete example
let Source = Folder.Files("YourFolderWithFilesToMerge"), #"Added Custom" = Table.AddColumn(Source, "TablesFromFile", each Table.Combine( List.Transform( Excel.Workbook([Content])[Data], each Table.PromoteHeaders(_)))), CombineAllFiles = Table.Combine(#"Added Custom"[TablesFromFile]) in CombineAllFilesCopy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hi Jimmy,
Thank you for the sharing and it works now!
Can I can have a column to indicate the name of worksheet so i know the records are belong to which bank?
Attached with the code because i did some row filters.
let Source = Folder.Files("C:\Users\Sharepoint\Cash Book"), #"Added Custom" = Table.AddColumn(Source, "TablesFromFile", each Table.Combine( List.Transform( Excel.Workbook([Content])[Data], each Table.PromoteHeaders(_)))), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Name] = "CompanyA Cash Book 2020.xlsx" or [Name] = "CompanyB Cash Book 2020.xlsx")), CombineAllFiles = Table.Combine(#"Filtered Rows"[TablesFromFile]), #"Removed Other Columns" = Table.SelectColumns(CombineAllFiles,{"Transaction Date", "Description", "Amount", "Category", "Balance", "Value Date", "PIC"}), #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Transaction Date", type date}, {"Description", type text}, {"Amount", type number}, {"Category", type text}, {"Balance", type number}, {"Value Date", type date}, {"PIC", type text}, {"(formula purposes)", type text}}), #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([Amount] <> null)) in #"Filtered Rows1"- Jimmy801
Hello Anonymous
this code now adds the sheet name before combining
let Source = Folder.Files("YourFolderWithFilesToMerge"), #"Added Custom" = Table.AddColumn(Source, "TablesFromFile", each Table.Combine(Table.AddColumn(Excel.Workbook([Content]), "WithSheetName", (add)=> Table.AddColumn(Table.PromoteHeaders(add[Data]),"Sheet name", each add[Name] ))[WithSheetName])), CombineAllFiles = Table.Combine(#"Added Custom"[TablesFromFile]) in CombineAllFilesCopy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyThank you, it helped. But there is a problem i am facing. I want to add File Name in a column as well as we added sheet name. it will be easier for me to give reference to. Kindly respond at earliest.
