Forum Discussion

unowho14's avatar
unowho14
Regular Visitor
3 years ago
Solved

Multiple Condition SelectRows

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

 

TimeEntries

PersonIDEntryDate
0001238/1/2023
0001238/2/2023

 

PayRates

PersonIDEffectiveDateRate
0001238/2/202220
0001238/2/202325
0009878/2/202330

 

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

PersonIDEntryDateRate
0001238/1/202320
0001238/2/202325

 

 

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?

 

Need Help
  • rubayatyasmin's avatar
    rubayatyasmin
    3 years ago

    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" )...
     

3 Replies

  • rubayatyasmin's avatar
    rubayatyasmin
    Community Champion
    3 years ago

    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's avatar
      unowho14
      Regular Visitor
      3 years ago

      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"
  • sevenhills's avatar
    sevenhills
    Super User
    3 years ago

    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)

     

    🙂