Consulting

Results 1 to 9 of 9

Thread: Power Query: Which technique will get me the same results as XLOOKUP?

  1. #1
    VBAX Regular
    Joined
    Apr 2017
    Location
    Atlanta
    Posts
    81
    Location

    Question Power Query: Which technique will get me the same results as XLOOKUP?

    The final output report will merge 4 tables.

    The issue: which technique to use in PQ that will give me the same results as XLOOKUP

    1. tblLMS (completed learning)
    2. tblCourse (this is the lookup table)


    In order to create a pivot table:
    I used the unique formula to create a list of original course titles/IDs (this was a hodgepodge of course titles/IDs and none of it followed best practices)
    I then matched the LMS course name/ID to the correct course titles/IDs

    I need PQ to give me the same results as xlookup. Look at course title and find the matching correct course title display the correct course title, same for the course ID.

    Thanks in advance for your time and advise.
    Last edited by jnix612; 05-13-2024 at 11:38 AM. Reason: add attachment sample file

  2. #2
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    348
    Location
    Perhaps with DISTINCT or GROUP BY.

    Want to provide sample data? Could attach file to post.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    VBAX Regular
    Joined
    Apr 2017
    Location
    Atlanta
    Posts
    81
    Location
    Here is the sample data.Sample Data.xlsx

  4. #4
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    348
    Location
    Query building is so simple in Access. As powerful as PQ is, I do find it challenging to construct SQL.

    If this were Access I could just show you the SQL statement.

    I did confirm that PQ has GROUP BY which can return a list of distinct course titles/IDs.

    If you want to associate "match" columns in Course with rows in LMS, problem is that a title/ID in Course can have more than one "match" values. Which should be used? I don't see how a list of distinct title/IDs will be useful. Example:

    Course Title Course ID Course Title Match Course ID Match
    Advanced Cost Principles ORG-DIV-ACP Internal Controls for the Federal Grants Community ORG-DIV-ICFC
    Advanced Cost Principles ORG-DIV-ACP Introduction to Grants & Cooperative Agreements ORG-DIV-IGCA
    Advanced Cost Principles ORG-DIV-ADVCSTPRN-2019 Monitoring Federal Grants and Cooperative Agreements ORG-DIV-MFGCA
    Advanced Cost Principles ORG-DIV-ACP Understanding National Policy Requirements Affecting Grants ORG-DIV-NPRAG-2017

    You say you want to merge 4 tables but provide only 2 tables.
    Last edited by June7; 05-13-2024 at 12:42 PM.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    VBAX Regular
    Joined
    Apr 2017
    Location
    Atlanta
    Posts
    81
    Location
    Yes that is correct there are 4 tables. I know how to use the PQ merge function. I only sent what I was having an issue with.

    Thank you so much for your help. I will start working with this now.

  6. #6
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    348
    Location
    Edited my previous post probably after you read it. Might review again.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    VBAX Regular
    Joined
    Apr 2017
    Location
    Atlanta
    Posts
    81
    Location
    I saw your edited post. Thank you. The spreadsheet I attached contained test data. In my original spreadsheet, neither the original nor the match column show duplicate values. I used "unique" in Excel, which removed duplicates. I had to generate too much fake data to send you the spreadsheet, and I may not have done a good job.

    Thank you for your help. I am good for now.

  8. #8
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,744
    Location
    Quote Originally Posted by jnix612 View Post
    The issue: which technique to use in PQ that will give me the same results as XLOOKUP
    Merging tables.
    Be as you wish to seem

  9. #9
    Just use WS formulas, requires some updates as sheets change
    Last edited by georgiboy; 06-04-2024 at 06:47 AM. Reason: Removed spam

Tags for this Thread

Posting Permissions

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