• 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

Source in Power Query|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Source in Power Query|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 QuerySource in Power Query
sp_PrintTopic sp_TopicIcon
Source in Power Query
Avatar
Jason Gustav

New Member
Members
Level 0
Forum Posts: 2
Member Since:
September 20, 2021
sp_UserOfflineSmall Offline
1
September 20, 2021 - 7:30 am
sp_Permalink sp_Print

Hi, I am new to power query. I hope this makes sense.

I would like to create a power query function that looks up a value in another table (lets call it table2) and then brings that into the source table (lets call it table1) and performs some calculations. I am thinking joining tables might be the best way to handle this.

I would like to be able to use the function on different tables that I import. The Source tables could be different file types, xls, csv, etc and have different names. The problem I am having is defining the Source which I think is needed to use the Table.NestedJoin function?

Is there a way to define the Source as the Active Table? 

I guess what I am wanting to know is if there is anyway to omit z in "(col1 as any, col2 as any, z as table)=>let "

I found a way to lookup values in another table using the List function without having to specify the Source table name, but the performance is really slow when I load it to the data model. I don't know why. 

Thank you.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4447
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
September 20, 2021 - 9:31 am
sp_Permalink sp_Print

Hi Jason,

Welcome to our forum! You're on the right track, you need to create a join between the two tables, which is the equivalent of a VLOOKUP in Excel terms. This is easily done using the Merge tables icon.  From there you can perform the other steps like adding functions and cleaning the data. Here are some tutorials to check out:

https://www.myonlinetraininghu.....ry-vlookup

https://www.myonlinetraininghu.....-functions

https://www.myonlinetraininghu.....ch-vlookup

Hope that points you in the right direction.

Mynda

Avatar
Jason Gustav

New Member
Members
Level 0
Forum Posts: 2
Member Since:
September 20, 2021
sp_UserOfflineSmall Offline
3
September 20, 2021 - 10:53 am
sp_Permalink sp_Print

Thank you for the links. I tried creating a custom function from the second link but it is really slow or doesn't work if I have a large data set (millions of records) so I think joining might be faster. I tried adding table.buffer and list.buffer but it didn't help. 

I periodically get files that I need to process. Each file is different but all have the same two variables of interest in col1 and col2. 

When creating a custom function, is there a way I can avoid having to put the "Source" when doing the Join or set it to the table that is currently active/open in power query.

I don't want this:

"(col1 as any, col2 as any, z as table)=>let "

I want something like this:

 "(col1 as any, col2 as any)=>let "
ActiveTable=Table that I have active in PQ
MyJoin=Table.NestedJoin(ActiveTable, key1 as any, table2 as any, key2 as any, newColumnName as text, optional joinKind)

I want to be able to create a Custom Column and be able to select col1 and col2 on any data that I get and import into power query.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Velouria, Jack Brett, Rocco Pinneri, LAFONSO HERNANDEZ
Guest(s) 10
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:
LAFONSO HERNANDEZ
Hayden Hao
Angela chen
Sean Moore
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Ray-Yu Yang
George Shihadeh
Forum Stats:
Groups: 3
Forums: 24
Topics: 6211
Posts: 27239

 

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