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

[jnix612]

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


tblLMS (completed learning)
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. :friends:

[June7]

Perhaps with DISTINCT or GROUP BY.

Want to provide sample data? Could attach file to post.

[jnix612]

Here is the sample data.31568

[June7]

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.

[jnix612]

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. :friends:

[June7]

Edited my previous post probably after you read it. Might review again.

[jnix612]

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. :friends:

[Aflatoon]

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

Merging tables. :)

[namdosan1409]

Just use WS formulas, requires some updates as sheets change