How to combine existing worksheets using Power Query's Advanced Editor

We used to adopt VBA to combine data tables from multiple worksheets or workbooks. However, if adopt Power BI, such work could be done without any code and be more intuitive.

• With Power Query
• To combine data tables from multiple workbooks
• Combining Data From Multiple Excel Workbooks With Power Query–The Easy Way!
• To combine data tables from multiple worksheets
• Power Query - Combine Tables
• With 3rd party tools
• RDBMerge

The way mentioned in this article, Power Query - Combine Tables, could be leveraged for most purposes. However, it still costs much time to create the data connections in advance, and those created data connections might be meaningless afterward.

After digging out the programming language, M Language, used in Power Query, we could use Power Query's advance editor to combine worksheets easier. The following example shows the way to combine 20 data tables:

let 

Source20 = Excel.CurrentWorkbook(){[Name="Table20"]}[Content],    

Source19 = Excel.CurrentWorkbook(){[Name="Table19"]}[Content],    

Source18 = Excel.CurrentWorkbook(){[Name="Table18"]}[Content],    

Source17 = Excel.CurrentWorkbook(){[Name="Table17"]}[Content],    

Source16 = Excel.CurrentWorkbook(){[Name="Table16"]}[Content],    

Source15 = Excel.CurrentWorkbook(){[Name="Table15"]}[Content],    

Source14 = Excel.CurrentWorkbook(){[Name="Table14"]}[Content],    

Source13 = Excel.CurrentWorkbook(){[Name="Table13"]}[Content],    

Source12 = Excel.CurrentWorkbook(){[Name="Table12"]}[Content],    

Source11 = Excel.CurrentWorkbook(){[Name="Table11"]}[Content],    

Source10 = Excel.CurrentWorkbook(){[Name="Table10"]}[Content],    

Source9 = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],    

Source8 = Excel.CurrentWorkbook(){[Name="Table8"]}[Content],    

Source7 = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],    

Source6 = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],    

Source5 = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],    

Source4 = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],    

Source3 = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],    

Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],    

Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],    

CombineData = Table.Combine({Source20, Source19, Source18, Source17, Source16, Source15, Source14, Source13, Source12, Source11, Source10, Source9, Source8, Source7, Source6, Source5, Source4, Source3, Source2, Source1})

in   CombineData

Hope this could help you to save much time when combining data tables.

References

• Learn about Power Query formulas
• Power Query Workshop