• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Append tables and named ranges from multiple workbooks|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Append tables and named ranges from multiple workbooks|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryAppend tables and named ranges from…
sp_PrintTopic sp_TopicIcon
Append tables and named ranges from multiple workbooks
Avatar
jaryszek
Member
Members
Level 0
Forum Posts: 183
Member Since:
February 1, 2019
sp_UserOfflineSmall Offline
1
September 21, 2021 - 3:27 pm
sp_Permalink sp_Print sp_EditHistory

Hi Guys,

i have table across multiple workbooks:

Col1 Col2 Col3
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:

Col1 Col2 Col3 Surname Month
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 and whole loyout is tricky:

Screenshot_223.pngImage Enlarger

How can i do this? 
Can you please help?

Best,
Jacek

sp_PlupAttachments Attachments
  • sp_PlupImage Screenshot_223.png (20 KB)
Avatar
jaryszek
Member
Members
Level 0
Forum Posts: 183
Member Since:
February 1, 2019
sp_UserOfflineSmall Offline
2
September 23, 2021 - 2:38 pm
sp_Permalink sp_Print

Anyone?

Jacek

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
3
September 23, 2021 - 5:16 pm
sp_Permalink sp_Print

You have already a topic here: https://www.myonlinetraininghu.....on-the-fly

The following expression works with both tables and named ranges:

Source= Excel.CurrentWorkbook(){[Name="NamedRange"]}[Content]

Source= Excel.CurrentWorkbook(){[Name="TableName"]}[Content]

A new topic will not give you a different solution 🙂 , that's just the way things work in PQ.

Avatar
jaryszek
Member
Members
Level 0
Forum Posts: 183
Member Since:
February 1, 2019
sp_UserOfflineSmall Offline
4
September 24, 2021 - 3:18 pm
sp_Permalink sp_Print sp_EditHistory

Sorry, 

this is not helpful. 

i am merging multiple workbooks together and want to get exactly what i wrote.
What power query will be for this? 

Named ranges what you are referring working for specific workbook (the same where power query is).

Here i want to use GetFolder method to merge all data but from external workbooks (merge tables plus named ranges all together in one query).

Please help anybody,
Jacek

I am attaching workbooks

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
5
September 24, 2021 - 4:23 pm
sp_Permalink sp_Print

You cannot connect to named ranges from external books.
All you can do is to convert the named range into a table with same name, Then query the folder where you have all workbooks.

Avatar
jaryszek
Member
Members
Level 0
Forum Posts: 183
Member Since:
February 1, 2019
sp_UserOfflineSmall Offline
6
September 24, 2021 - 4:55 pm
sp_Permalink sp_Print

Thanks.

It is not possible to query named ranges? 

And combining tables is only for whole worksheet, not exact range where table is? 

How can i do this approach with 2 tables? Can you provide example ?

 

Best,
Jacek

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
7
September 24, 2021 - 7:19 pm
sp_Permalink sp_Print sp_EditHistory

You cannot connect to named ranges from external books.

Why don't you add a new column in the source table you have, with the formula=NamedRangeName

Avatar
jaryszek
Member
Members
Level 0
Forum Posts: 183
Member Since:
February 1, 2019
sp_UserOfflineSmall Offline
8
September 24, 2021 - 8:23 pm
sp_Permalink sp_Print

Hmm this is idea! 

But few questions still: 

1) i see that power query sees named ranges and not seeing tables:

Screenshot_227.pngImage Enlarger

So still retriving tables equails to retrive whole worksheet?

I think it is possible to retrive somehow named ranges if power query sees them

Best,
Jacek

sp_PlupAttachments Attachments
  • sp_PlupImage Screenshot_227.png (163 KB)
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
9
September 24, 2021 - 9:21 pm
sp_Permalink sp_Print

Ok, seems like it reads named ranges, you can right click the first table and add as a new query, same for second table, drill down until the result of these new queries are text, not lists, then you'll be able to add them as new column formulas in the third table.

Avatar
jaryszek
Member
Members
Level 0
Forum Posts: 183
Member Since:
February 1, 2019
sp_UserOfflineSmall Offline
10
September 25, 2021 - 9:27 pm
sp_Permalink sp_Print sp_EditHistory

Ok Guys,

maybe will be useful for you, i solved the issue (with help from outside)

AllFiles - first query:

let
Source = Folder.Files("C:\Users\Luke\Documents\Power Query\Files"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))
in
#"Expanded Table Column1"

retriving named range:

let
Source = AllFiles,
#"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Column1"}, {"Data.Column1"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Data", each ([Name] = "R_Surname")),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Name"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Data.Column1", "Nazwisko"}})
in
#"Renamed Columns"

retiving table:

let
Source = AllFiles,
#"Filtered Rows2" = Table.SelectRows(Source, each ([Name] = "Sheet1")),
#"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows2", "Data", {"Column1", "Column2", "Column3"}, {"Data.Column1", "Data.Column2", "Data.Column3"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Data", each ([Data.Column1] <> null and [Data.Column1] <> "Month" and [Data.Column1] <> "Surname"))
in
#"Filtered Rows"

and last step is just to merge queries 🙂 

Best wishes for all Power Query developers!,
Jacek

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Velouria, YANINA TRIGO
Guest(s) 9
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Sopi Yuniarti
LAFONSO HERNANDEZ
Hayden Hao
Angela chen
Sean Moore
John Chisholm
Forum Stats:
Groups: 3
Forums: 24
Topics: 6216
Posts: 27250

 

Member Stats:
Guest Posters: 49
Members: 31897
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.