Multiple Condition SelectRows

[unowho14]

I am trying to get 2 conditions to work in a SelectRows.  I have 2 tables:

TimeEntries

PersonID	EntryDate
000123	8/1/2023
000123	8/2/2023

PayRates

PersonID	EffectiveDate	Rate
000123	8/2/2022	20
000123	8/2/2023	25
000987	8/2/2023	30

I am trying to get the Rate from the PayRates table to show in the TimeEntries table if the PersonID matches and the Effective Date of the Rate is less than or equal to the Time Entry date. 

Desired Result: 

TimeEntries

PersonID	EntryDate	Rate
000123	8/1/2023	20
000123	8/2/2023	25

If I do each query by themselves, they work.  If I combine the two, they don't work and I get an error that essentially says "Cannot convert PersonID to type logical."

Works:

Table.AddColumn(#"Removed columns", "Rate", each let PersonID = [PersonID] in
Table.Max(
Table.SelectRows(#"PayRates",each [PersonID] = PersonID), "EffectiveDate")[Rate])

Works: 

Table.AddColumn(#"Removed columns", "Rate", each let EntryDate = [EntryDate] in
Table.Max(
Table.SelectRows(#"PayRates",each [EffectiveDate] <= EntryDate), "EffectiveDate")[Rate])

Doesn't Work:

Table.AddColumn(#"Removed columns", "Rate", each let EntryDate = [EntryDate] and PersonID = [PersonID] in
Table.Max(
Table.SelectRows(#"PayRates",each ([EffectiveDate] <= EntryDate and [PersonID] = PersonID)), "EffectiveDate")[Rate])

What am I doing wrong in combining the two conditions?

[sevenhills]

In addition to @rubayatyasmin soluton, I will recommend to add try logic. 

Custom1 = Table.AddColumn( #"Changed Type", "Rate", each 
                try (
                let cr_PersonID = [PersonID], 
                    cr_EntryDate = [EntryDate]
                in
                    Table.Max(Table.SelectRows(#"PayRates",each cr_PersonID = [PersonID] and [EffectiveDate] <= cr_EntryDate), "EffectiveDate")
                )[Rate] 
                otherwise null
            , Int64.Type)

🙂 

[rubayatyasmin · Accepted solution]

Hi, @unowho14 

don't we use ',' inside the let statement? try removing the 'and'

let

EntryDate = [EntryDate],

PersonID = [PersonID]

and try removing the extra bracket, this should work without the bracket. 

...each [EffectiveDate] <= EntryDate and [PersonID] = PersonID), "EffectiveDate" )...

[unowho14]

Hi unfortunately my Power Query got deleted by accident and I'm banging my head against the wall again on this.  Below is not working.  I am simply trying to pull the rate from the second table into the first table with 2 conditions.  I'm not sure why this is so hard for me..

Table 1: TimeEntries (Grouped rows in below)

Field: Period_From

Field: Resource_No

Table 2: Rates

Field: Effective_Date

Field: Resource_No

Field: Rate

  #"Add Rate" = Table.AddColumn(#"Grouped rows", "Rate", each let Resource_No = [Resource_No], Period_From = [Effective_Date] in

Table.Max(

Table.SelectRows(#"PayRates",each [Effective_Date] <= Period_From and [Resource_No] = Resource_No), "Effective_Date")[Rate])

in

  #"Add Rate"