File Download: https://www.excel-university.com/yt_8...

This video shows how to use Power Query to combine multiple workbooks into a single workbook. This approach supports the situation where workbooks may contain multiple worksheets. Provided the column labels are the same in each, Power Query will line up the data columns consistently.

Copy the following M Code:

let
MyFiles = Excel.CurrentWorkbook(){[Name="filepath"]}[Content]{0}[Column1],
Source = Folder.Files(MyFiles),
#"Added Custom" = Table.AddColumn(Source, "Sheets", each Excel.Workbook(File.Contents([Folder Path]&[Name]))),
#"Expanded Sheets" = Table.ExpandTableColumn(#"Added Custom", "Sheets", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"}),
#"Expanded Data" = Table.ExpandTableColumn(#"Expanded Sheets", "Data", {"Column1", "Column2"}, {"Column1", "Column2"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Data", {"Column1", "Column2"})
in
#"Removed Other Columns"


Chapters in this video:
00:00 - Introduction
00:16 - File Path
00:46 - Power Query
02:03 - Customize


🚀 Unlock hidden Excel tricks and hacks, and become a time-saving Excel pro 👉 excel-university.com/challenge⚡️🕒 Say goodbye to late nights and spreadsheet struggles!

Subscribe here 👉 https://www.youtube.com/c/ExcelUniver... and turn on all notifications 🔔 for MS Excel time hacks that will make you more productive!

Also, check out these videos with time saving Excel Hacks:

Excel PivotTables for Beginners    • Excel PivotTables for Beginners (2024)  

Watch VLOOKUP Hacks Playlist here    • VLOOKUP Hacks Series  

Watch VLOOKUP vs SUMIFS Playlist here    • VLOOKUP vs SUMIFS Series  

Watch Dynamic Arrays playlist here    • Dynamic Arrays Series  

Watch Treasure Maps playlist here    • Treasure Maps Series  

Watch Slow to Fast playlist here    • Slow to Fast Series