November 8, 2021
I am trying to combine data from multiple sheets into a single Master sheet. I am able to do this using a Query with the source being either Excel.CurrentWorkbook() or using Table.Combine().
My question is, what is the difference between these methods? From what I can tell, using Table.Combine() is more laborious, as you have to setup each table in advance to allow connections. So Excel.CurrentWorkbook() seems much better, but perhaps there is some downside I am missing (e.g. a performance overhead).
I have included a sample file which shows the output of both methods on the colored tabs.
Thanks in advance!
July 16, 2010
Welcome to our forum!
I think if there were any performance differences it would be negligible and probably depend on the complexity of the file. e.g. if the file had a lot of worksheets then Excel.CurrentWorkbook might be slower than you manually creating queries from the sheets you were interested in and then combining them, but you'd have to test it on a case by case basis.
Of course the obvious, that you've already pointed out, is that with Excel.CurrentWorkbook you don't have to manually create the individual queries first.