Power Query or Pivot on Two Workbooks

[MikeL]

Hi,
I have yet to see how to work with tables in multiple workbooks. I have Actuals with chart strings in one Workbook. Budgets with chart strings in another. It seems in order to do a merge, append or create a data model that all examples I've seen have both tables in 1 workbook.

Can someone take me thru the steps using two workbooks?


Mike

 

[sandy666]

all examples I've seen have both tables in 1 workbook

because it's easier to manage and you don't have to update the path to other files or file names if anything changes

 

[MikeL]

because it's easier to manage and you don't have to update the path to other files or file names if anything changes

Thanks, but I have 2 source files from different folks. Do I really have to put both source files in the same workbook?

 

[sandy666]

Do I really have to put both source files in the same workbook?

No, of course not
Just use for each file: Data - New Query - From File - From Workbook
then you can do with these tables what you want

 

[MikeL]

No, of course not
Just use for each file: Data - New Query - From File - From Workbook
then you can do with these tables what you want

So I have done that step via Power Query and have two tables but in seperate workbooks. how to combine for next step?

 

[sandy666]

scenario:
file1 with data1
file2 with data2
brand new, blank, summary_file
from summary_file: Data - New Query - From File - From Workbook ~ file1
and again: Data - New Query - From File - From Workbook ~ file2
and data from two files will be in one (summary_file) workbook then you can do what you want with tables, append merge, etc

 

[MikeL]

thank you