Append tables and named ranges from multiple workbooks

[jaryszek]

Hi Guys,

i have table across multiple workbooks in xlsb:

Col1Col2Col3

1	1	1
2	2	2

plus named ranges in each: R_Surname and R_Month.

-------------------------------------------------------------

Now i would like to append all tables into one master workbook and get:

Col1Col2Col3SurnameMonth

1	1	1	Smith	January
2	2	2	Smith	January
3	3	3	Johnson	December
4	4	4	Johnson	December

Smith and January are coming from example workbook1 (table1.xlsb) and Johnson and December are coming from Table2.xlsb.

what is important that name ranges are in the same worksheet as table in each workbook. 

How can i do this? 

My google drive files:

https://drive.google.com/drive/folders/1VVABEwqkTPyhFHmhF4oaUIavsrHxm8zm?usp=sharing

Can you please help?

Best,
Jacek

[jaryszek]

Thank you so much! 

[Syndicate_Admin · Accepted solution]

Hi Jacek,

When you load one of your excel sheets into Power Query, you'll want to bring in all the named ranges and Sheet1:

My Power Query isn't picking up the table on Sheet1, so the first thing to do is remove the top 4 rows (with Remove Rows) and then promote the new top row to being column headers (with Use First Row as Headers) so that our Sheet1 query looks like the table on the worksheet:

In order to get the value from each named range to appear as a column on our "Sheet1" query, we need to add a Custom Column with the following formula:

We can repeat this process to get the Month field into this query too:

From here, we can just repeat the same process for any other workbooks (copying and pasting from the Advanced Editor if necessary), and then, becuase all of the queries have the same structure, we can Append them (using Append Queries) once we've processed them all to get it into a single table that looks like your master table:

[AlanHarman-Box]

Hi Jacek,

When you load one of your excel sheets into Power Query, you'll want to bring in all the named ranges and Sheet1:

My Power Query isn't picking up the table on Sheet1, so the first thing to do is remove the top 4 rows (with Remove Rows) and then promote the new top row to being column headers (with Use First Row as Headers) so that our Sheet1 query looks like the table on the worksheet:

In order to get the value from each named range to appear as a column on our "Sheet1" query, we need to add a Custom Column with the following formula:

We can repeat this process to get the Month field into this query too:

From here, we can just repeat the same process for any other workbooks (copying and pasting from the Advanced Editor if necessary), and then, becuase all of the queries have the same structure, we can Append them (using Append Queries) once we've processed them all to get it into a single table that looks like your master table: