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"