Forum Discussion

tempranello's avatar
tempranello
Advocate I
10 years ago
Solved

Power Query custom column based on two criterion

Hello there   I've come out the other end of M is for (Data) Monkey and plenty of googling and mucking about to resolve this myself, but to no avail.  I'm hoping that you can help me.   I've a qu...
  • tempranello's avatar
    10 years ago

    Hi there

     

    I'm learning alot about Power Query :-)

     

    I have solved this little problem.  I'll share my result in case it helps others:

     

    My Timesheets query custom column calls the function:  fnLookupNameDateMatch([Name],[Date],"Team",#"Staff Movements")

     

    The function is thus:

    /*  Filter the lookup_table by lookup_name and lookup_date, and return the value in the specified column*/
    (lookup_name as text, lookup_date as date, return_column as text, lookup_table as table) as any =>
    let
            FilterTable = Table.SelectRows(lookup_table, each Text.Contains([Name], lookup_name) and [Joined] <= lookup_date),
            ReturnResult = Record.Field(Table.First(Table.Sort(FilterTable,{"Joined",Order.Descending})), return_column)
    in ReturnResult

     

    Thanks to all for reading and replying.