cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jaryszek
Helper III
Helper III

Append tables and named ranges from multiple workbooks

Hi Guys,

i have table across multiple workbooks in xlsb:

Col1Col2Col3

111
222

plus named ranges in each: R_Surname and R_Month.

jaryszek_2-1632466687007.png

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

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

Col1Col2Col3SurnameMonth

111SmithJanuary
222SmithJanuary
333JohnsonDecember
444JohnsonDecember

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

1 ACCEPTED SOLUTION
Syndicate_Admin
Administrator
Administrator

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:

AlanHarmanBox_0-1632475678757.png

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:

AlanHarmanBox_1-1632475806619.png

 

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:

AlanHarmanBox_2-1632475961887.png

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

AlanHarmanBox_3-1632476610867.png

 

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:

 

AlanHarmanBox_4-1632476819021.png

 

 

View solution in original post

3 REPLIES 3
jaryszek
Helper III
Helper III

Thank you so much! 

Syndicate_Admin
Administrator
Administrator

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:

AlanHarmanBox_0-1632475678757.png

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:

AlanHarmanBox_1-1632475806619.png

 

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:

AlanHarmanBox_2-1632475961887.png

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

AlanHarmanBox_3-1632476610867.png

 

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:

 

AlanHarmanBox_4-1632476819021.png

 

 

View solution in original post

AlanHarman-Box
Regular Visitor

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:

AlanHarmanBox_0-1632475678757.png

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:

AlanHarmanBox_1-1632475806619.png

 

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:

AlanHarmanBox_2-1632475961887.png

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

AlanHarmanBox_3-1632476610867.png

 

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:

 

AlanHarmanBox_4-1632476819021.png

 

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors