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

 

🙂