Results 1 to 3 of 3

Thread: VLOOKUP functionality in Power Query

  1. #1
    Neophyte zz_zz's Avatar
    Join Date
    Feb 2021
    Posts
    1
    Articles
    0
    Excel Version
    2016

    VLOOKUP functionality in Power Query



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am new to Power Query and I am having difficulties recreating the functionality of VLOOKUP from Excel.

    I have two tables in Power Query. One is product sales and the other is product cost. I need to bring the cost to the sales table. The issue is that costs change over time so each product comes with a cost and a particular month. I merged columns (Month and SKU) so both tables have identical columns and now I just need to figure out how to merge them. I tried merging but then I am getting a lot more rows than my original sales table contain (not sure why though).
    Attached Files Attached Files

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,599
    Articles
    0
    Excel Version
    Office 365 Subscription
    Probably using the wrong join type. I loaded table 1 to connection only, then loaded table 2 and merged with table 1 on product and month (right outer join).

    Be aware that there is a trailing space after Jan in B6.
    Attached Files Attached Files
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,449
    Articles
    44
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hi zz_zz,

    What kind of VLOOKUP are you trying to use? Exact or Approximate? It looks like you always have each product for each month in the pricing table, so I'm going to assume it's Exact.

    The secret is to base your join on two columns (Product-Month) instead of one. To do that, use the Left Outer join, but when making the merge hold down your CTRL key to select multiple columns. (Just make sure you go with a consistent order.) One issue I see here is that product C's "Jan" entry is "Jan " (with a space), which means you won't get an exact match, as your data is dirty. You will need to fix that, either by trimming the columns prior to merging them, or cleaning up the source data.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •