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

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.

[June7]

Perhaps with DISTINCT or GROUP BY.

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

[jnix612]

Here is the sample data.Sample Data.xlsx

[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.

[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.

[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