none
Need Exact Vlookup in Power Query RRS feed

  • Question

  • I have 5 excel files.
    1. MasterDump (have 200000 records) (Say example, lookupvalue=RollNo)
    2. Child1 (1050 records)
    3. Child2 (1100 records)
    4. Child3 (20000 records)
    5. Child4 (10500 records)

    All child excel sheets contain RollNo & i want to apply MERGE operation & fetch relavant Gender from all child sheets

    - I want to maintain these 200000 records in masterdump & want to apply merge and find the relavant match for all Child excel sheets
    - If match find then i need Gender. Else let it be NULL (only first match is enough. no duplicates/increase in masterDump file)
    - MasterDump records should not be disturbed (while i am applying merge/join, duplicate records getting created in MasterDump)

    In one  word i want the exact vlookup concept need to be applied here. but i am getting duplicates & masterdump getting violated.

    Expecting your help to achieve it



    • Edited by KaranTN Tuesday, July 17, 2018 10:48 AM
    Tuesday, July 17, 2018 4:27 AM

Answers

  • BroadBent,

    Thanks for your reply. But i am looking the vlookup to be implemented in POWER QUERY (which means MERGE operation)

    Hi Karan,

    Power Query has no built-in equivalent to the Excel VLOOKUP function. Joining (merging) tables works differently:

    - VLOOKUP returns a scalar value. Joining returns all matching records (and not just the first).
    - VLOOKUP can do an approximate match (if the lookup column is correctly ordered). Joining does not perform approximate matches.
    - If there is no match, VLOOKUP returns an error. Joining can return nulls.
    - Joining can match based on multiple column values. VLOOKUP matches are based on a single lookup column.

    What you can do in the Power Query UI:

    1) For each child table, select Home-->Remove Rows-->Remove Duplicates, where the roll number and gender define the distinctness of the rows.
    2) Select one of the child tables. Select Home-->Append Queries-->either of the two options.
    3) In the Append dialog box, choose "Three or more tables" and choose the child tables to append.
    4) Select the MasterDump table and select Home-->Merge Queries-->either of the two options.
    5) In the merge dialog box, the MasterDump table should be displayed at the top. In the preview window, click the RollNo column.
    6) In the drop down box below the MasterDump preview window, choose the append query created in step 3.
    7) In the new preview window that appears, click the RollNo column.
    8) In the Join Kind drop down box, choose Left Outer (should be displayed by default).

    If the same roll numbers occur in multiple child tables, perform steps 2 and 3 before step 1.

    • Marked as answer by KaranTN Sunday, July 22, 2018 11:23 AM
    Wednesday, July 18, 2018 12:49 PM

All replies

  • Make sure you use the setting to find exact values - the last parameter in the VLOOKUP should be FALSE, which means "do not use approximate match").

    Without knowing how your spreadsheet is configured, the function should look something this:

    =VLOOKUP(B2,Child1!B2:C956,2,FALSE)

    (Assuming your RollNo values are in Column B and the data to search in the Child1 sheet is in the range B2:1051)

    For ref. see https://support.office.com/en-us/article/VLOOKUP-function-0BBC8083-26FE-4963-8AB8-93A18AD188A1


    Ian Broadbent

    Tuesday, July 17, 2018 5:21 AM
  • BroadBent,

    Thanks for your reply. But i am looking the vlookup to be implemented in POWER QUERY (which means MERGE operation)

    Tuesday, July 17, 2018 10:47 AM
  • BroadBent,

    Thanks for your reply. But i am looking the vlookup to be implemented in POWER QUERY (which means MERGE operation)

    Hi Karan,

    Power Query has no built-in equivalent to the Excel VLOOKUP function. Joining (merging) tables works differently:

    - VLOOKUP returns a scalar value. Joining returns all matching records (and not just the first).
    - VLOOKUP can do an approximate match (if the lookup column is correctly ordered). Joining does not perform approximate matches.
    - If there is no match, VLOOKUP returns an error. Joining can return nulls.
    - Joining can match based on multiple column values. VLOOKUP matches are based on a single lookup column.

    What you can do in the Power Query UI:

    1) For each child table, select Home-->Remove Rows-->Remove Duplicates, where the roll number and gender define the distinctness of the rows.
    2) Select one of the child tables. Select Home-->Append Queries-->either of the two options.
    3) In the Append dialog box, choose "Three or more tables" and choose the child tables to append.
    4) Select the MasterDump table and select Home-->Merge Queries-->either of the two options.
    5) In the merge dialog box, the MasterDump table should be displayed at the top. In the preview window, click the RollNo column.
    6) In the drop down box below the MasterDump preview window, choose the append query created in step 3.
    7) In the new preview window that appears, click the RollNo column.
    8) In the Join Kind drop down box, choose Left Outer (should be displayed by default).

    If the same roll numbers occur in multiple child tables, perform steps 2 and 3 before step 1.

    • Marked as answer by KaranTN Sunday, July 22, 2018 11:23 AM
    Wednesday, July 18, 2018 12:49 PM