Excel Power Query - How to validate source data using a Range validation table

Can anyone please help me on this?
I am trying to validate the source data in 3 columns BU - Act - Dept in the Range validation Data [BU - Beginning Act - End Account - Beginning Dept - End Dept] (screenshot attached).
BU is a single column in validation table but Act & Dept columns are range columns. I need to check if the BU - Act - Dept combo exists in Range data. So does Power query has any functionality to validate the source data using a nested join? This needs to be done in Excel power query and not formulas because the validation Range file has more than 1 million records.

Please note that Excel Power query is free excel addin which acts as a back end database for Excel.
Source-Data.xlsx
Range-Validation-file.xlsx
Anirudh KAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

abbas abdullaCommented:
Hi,
Can you share dummy data to ease the assistance?
Anirudh KAuthor Commented:
Hi, I have shared the dummy data files. Thanks!
abbas abdullaCommented:
Hi,
Please see the attached file.
Just change the file path in the Source line to the path where you store your files
1st Query: Range-Validation-file
Source = Excel.Workbook(File.Contents("C:\Users\abbasabdulla\Downloads\Range-Validation-file.xlsx"), null, true),

Open in new window


2nd Query Source-Data
Source = Excel.Workbook(File.Contents("C:\Users\abbasabdulla\Downloads\Source-Data.xlsx"), null, true),

Open in new window


3rd Query will return the result you want - ValidatedData
Power-Query-Validated-Data.xlsx
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
powerBI

From novice to tech pro — start learning today.