Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jaryszek
Post Patron
Post Patron

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
Post Patron
Post Patron

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

 

 

AlanHarman-Box
Frequent 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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors