Matching data between queries without merging in MQuery

[Styles1980]

Hi Everyone,

I am looking for some help with a current issues, Thank you in advance for any help you can provide.

I have 2 data queries pulling from different spreadsheets/api - I need to pull through the names of all individuals in query A that match criteria between the 2 queries, for example

Pulls through Bob if date from query 1 is >= effective start date in query 2 and role from query 1 = role from query 2.

If i merge queries I only get 1 match per row and need to pull through all possible matches.
If i pull Query 1 into Query 2 without merging I end up with 85 million rows which isnt feasible from a load perspective.

I have tried but get error that 3 arguements were passed when 2 expected.

= Table.SelectRows(#"Replaced Value1", "Custom", each ((Schedule[Date] >= [Effective Start] and Schedule[Level] = [Level] and Schedule[Capability] = [Spec] and Schedule[Role] = [Role] and Schedule[Availability] = "Available")))

Also tried the below in a custom column but everything pulls through as null

let valdate = [Effective Start],

valdate1 = Schedule[Date],

vallevel = [ResourceLevelSortOrder],

vallevel1 = Schedule[LevelSortOrder],

valspec = [Spec],

valspec1 = Schedule[Capability],

valrole = [Role],

valrole1 = Schedule[Role],

valname = Schedule[Name],

valavail = Schedule[Available],

userlist = Table.SelectRows(Schedule, each valdate1 >= valdate and vallevel1 = vallevel and valspec1 = valspec and valrole1 = valrole and valavail = "Available")

in 

userlist

Also tried this in a custom column but it would come through as a function rather then list

let

valdate = [Effective Start],

valdate1 = Schedule[Date],

vallevel = [ResourceLevelSortOrder],

vallevel1 = Schedule[LevelSortOrder],

valspec = [Spec],

valspec1 = Schedule[Capability],

valrole = [Role],

valrole1 = Schedule[Role],

valname = Schedule[Name],

valavail = Schedule[Available]

in 

each List.Select(valname,each (valdate1 >= valdate) and (vallevel1 = vallevel) and (valspec1 = valspec) and (valrole1 = valrole) and (valavail = "Available")))

Pictures of 2 queries
Query 1 list of names to show if other columns match query 2

Query 2 which i want the list to pull into after matching criteria between both queries

Recap - I need a list of names to populate from Query 1 when columns in both Query 1 & 2 match. 

[Styles1980]

Hi @Mahesh0016 
Did you have a chance to review the above and any possible solutions to obtain the list of relevant matches
Thanks

[Mahesh0016]

@Styles1980 Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Styles1980]

Hi @Mahesh0016 
Query 1 would look like this - a line per person for each day of the year except weekends.

Name	Date	Role	Level	Capability	LevelSortOrder	Availability
Ntba A	01/02/23	Engineer	Mid	Frontend (React)	3	Available
Ntba B	01/02/23	Engineer	Mid	Frontend (React)	3	Available
Ntba C	01/02/23	Engineer	Mid	Frontend (React)	3	Assigned

Query 2 This has the requirements of what we need to match against Query 1

Outcome would be each persons name next to the match, creating a new row for each entry. So for Project A we would have no matches, Project B we would have 2 rows with the names Ntba A and Ntba B, Project C no matches.

Project	Effective Start	Role	Level	Spec	ResourceLevelSortOrder
A	01/01/23	Engineer	Senior	Frontend (React)	4
B	21/03/23	Engineer	Mid	Frontend (React)	3
C	01/04/23	Engineering Manager	n/a	n/a	6