Combine all sheets with different names from multiple files

[Anonymous]

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?

[Jimmy801 · Accepted 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
    CombineAllFiles

Copy 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

[Anonymous]

Hi @Jimmy801 

I got it perfectly, thank you very much!

[Anonymous]

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 · Accepted solution]

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
    CombineAllFiles

Copy 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

[ZainnSaddique]

Thank 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. 

[Anonymous]

Hi AlB,

Thank you for the reply.

I have no problem to merge multiple worksheets from a single file, but my problem is to merge multiple files that each with multiple worksheets with different name.

Then the second file will be ComB BankA USD and etc.

[Jimmy801 · Accepted 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
    CombineAllFiles

Copy 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

[Anonymous]

Thank you very much!  It works

[AlB]

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