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?
 
abbas abdullaCommented:
Hi,
Can you share dummy data to ease the assistance?
0
 
Anirudh KAuthor Commented:
Hi, I have shared the dummy data files. Thanks!
0
 
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
0
All Courses

From novice to tech pro — start learning today.