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 PersonID EntryDate 000123 8/1/2023 000123 8/2/2023   PayRates PersonID EffectiveDate ...
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" )...
     
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"