• 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

Merge or lookup two nested tables|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Merge or lookup two nested tables|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 QueryMerge or lookup two nested tables
sp_PrintTopic sp_TopicIcon
Merge or lookup two nested tables
Avatar
leroy holmes
Member
Members
Level 0
Forum Posts: 6
Member Since:
March 21, 2019
sp_UserOfflineSmall Offline
1
August 9, 2022 - 10:54 am
sp_Permalink sp_Print

Hi,

I have a table. Within the table some of the columns have nested tables within them.

Column "TableFFromlisttest" has a nested table in each row. In that nested table is one column called "Column1".

TableFFromlisttest-1.jpgImage Enlarger

The data in column 1 of the nested table may vary in each row from ie contain any combinations of the three rows or only one of them

 

Column "levytable" also has a nested table in each row. In that nested table is two columns "Wording in Regs" & "Levy $"

levytable-1.jpgImage Enlarger

 

Somehow I need to combine or lookup or transform so that Column "TableFFromlisttest" nested table contains an added column "$" that pulls data or looks up from the other nested table.

The result would be a nested table Column A in each row.

Result.jpgImage Enlarger

Any help would be appreciated

sp_PlupAttachments Attachments
  • sp_PlupImage TableFFromlisttest.jpg (18 KB)
  • sp_PlupImage levytable.jpg (35 KB)
  • sp_PlupImage TableFFromlisttest-1.jpg (18 KB)
  • sp_PlupImage levytable-1.jpg (35 KB)
  • sp_PlupImage Result.jpg (18 KB)
Avatar
Jessica Stewart
Northern USA
Member
Members


Trusted Members
Level 0
Forum Posts: 202
Member Since:
February 13, 2021
sp_UserOfflineSmall Offline
2
August 11, 2022 - 1:17 am
sp_Permalink sp_Print

Phil gives a good way to do this in this video. If you need further assistance please upload an example file complete with an example of your desired result. It is helpful to manipulate the data to better understand your issue. Hope this helps.

Avatar
leroy holmes
Member
Members
Level 0
Forum Posts: 6
Member Since:
March 21, 2019
sp_UserOfflineSmall Offline
3
August 18, 2022 - 4:23 pm
sp_Permalink sp_Print

Hi Jessica,

Thanks for your tips. I tried Phil's method. and it does pull data from a table based on cell containing a 'value'.

However I'm trying to add another column to the nested table in the 'TableFFromlisttest' column which finds the 'Levy $' which I then get the maximum and return as a result the "Wording in Regs"

I've included a PBI file. I can do it by another method but would like to achieve the:

want-to-achieve.jpgImage Enlarger

 

I hope this makes it clearer

 

screen shots of the other bits

Data-Screenshot.jpgImage Enlarger

Levies-screenshot.jpgImage Enlarger

Levytable-column.jpgImage Enlarger

 

Table1Row2ColumnTableFFromlisttest.jpgImage Enlarger

Table1Row2ColumnTableFFromlisttest-1.jpgImage Enlarger

Result1.jpgImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage Data-Screenshot.jpg (11 KB)
  • sp_PlupImage Levies-screenshot.jpg (10 KB)
  • sp_PlupImage Levytable-column.jpg (27 KB)
  • sp_PlupImage Table1Row2ColumnTableFFromlisttest.jpg (14 KB)
  • sp_PlupImage Table1Row2ColumnTableFFromlisttest-1.jpg (14 KB)
  • sp_PlupImage Result1.jpg (25 KB)
  • sp_PlupImage want-to-achieve.jpg (37 KB)
Avatar
Jessica Stewart
Northern USA
Member
Members


Trusted Members
Level 0
Forum Posts: 202
Member Since:
February 13, 2021
sp_UserOfflineSmall Offline
4
August 19, 2022 - 12:44 am
sp_Permalink sp_Print

Unfortunately, I don't have Power BI so I can't see the data. Hopefully, someone else can help you out that can see the data. I'm sorry.

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 440
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
5
August 19, 2022 - 10:58 pm
sp_Permalink sp_Print sp_EditHistory

Hi Leroy,

I took a look at the pbix file but found it difficult to figure out what you are trying to achieve. Decided to copy the data and queries into Excel and added a few steps to the query that combines the Data and Levies tables before loading it back to Excel. It may or may not be what you want. 

If not, can you add a table to the Excel file (by typing) showing us what the end result should be?

Riny

Avatar
leroy holmes
Member
Members
Level 0
Forum Posts: 6
Member Since:
March 21, 2019
sp_UserOfflineSmall Offline
6
August 23, 2022 - 7:37 am
sp_Permalink sp_Print

Hi Riny,

Thanks for the reply. I tried to simplify it for better understanding of what I'm after.

[list from now] which contains a nested list or [TableFFromlisttest] which contains a nested table can be used. Either is ok. Because Phil's video suggests using List.PostitionOf is a faster method & I have a lot of data I'm wanting to achieve the result by not using a merge process.

 

List-from-row.jpgImage Enlarger

Single-column-table-from-row.jpgImage Enlarger

nested-levy-table.jpgImage Enlarger

Result I'm after is the nested table has an additional column[Levy $] which has the corresponding levy amount from some kind of a lookup

Result-required.jpgImage Enlarger

 

I'm then able able to then get the record with the maximum levy amount and continue on with further steps.

sp_PlupAttachments Attachments
  • sp_PlupImage List-from-row.jpg (21 KB)
  • sp_PlupImage Single-column-table-from-row.jpg (21 KB)
  • sp_PlupImage nested-levy-table.jpg (24 KB)
  • sp_PlupImage Result-required.jpg (16 KB)
Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 440
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
7
August 23, 2022 - 2:51 pm
sp_Permalink sp_Print

Hi Leroy,

Perhaps I'm just slow today but I still don't get it. You mention that you want "a nested table with a conditional column for Levy $". Isn't that exactly what you already have, as shown in the Result-required picture?

Avatar
leroy holmes
Member
Members
Level 0
Forum Posts: 6
Member Since:
March 21, 2019
sp_UserOfflineSmall Offline
8
August 30, 2022 - 7:16 am
sp_Permalink sp_Print

Hi Riny,

Apologies for the late reply. You are correct in that I achieved the result. However I went to and watched the link Jessica mentioned and it seemed to imply that using List.PositionOf was a more efficient/quicker way than using some kind of merge. As I have a large amount of data the current method takes 2.5 hours to update. I was hoping to find another way and then I could test it to see if indeed it was more efficient.

Thanks for you input.

Regards

Leroy

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Andy Kirby, Chandler Davis
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
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:
wahab tunde
Cong Le Duc
Faisal Bashir
Ivica Cvetkovski
Blaine Cox
Shankar Srinivasan
riyepa fdgf
Hannah Cave
Len Matthews
Kristine Arthy
Forum Stats:
Groups: 3
Forums: 24
Topics: 6205
Posts: 27210

 

Member Stats:
Guest Posters: 49
Members: 31878
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.