Power Query custom column based on two criterion

[tempranello]

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 query (Timesheets) that lists timesheet entries for staff.  Each record holds a name and a date of the entry.  I want to add a custom column for the team at the time the entry was made.

I've a second query (StaffMovements) that lists staff names, there team and the date they joined.  That table may contain multiple entries for a staff member who has moved teams while with the company.

The logic is thus:  Timesheets custom column = Get StaffMovements.Team where StaffMovements.Name = Timesheets.Name and StaffMovements.DateJoined <=Timesheets.Date

I'd then sort by date and return the Team column for the first result.

So far my custom column returns an error: 

An error occurred in the ‘’ query. Expression.Error: We cannot convert the value null to type Table.
Details:
    Value=
    Type=Type

My function (fnLookupNameDateMatch):

(staffName as text, joinedDate as date, lookupTable as table, returnColumn as text) as any =>
  let
    ReturnRow = Table.Max(
        Table.SelectRows(
            lookupTable, each Text.Contains([Name],staffName) and [Joined] <= joinedDate
        ),"Joined"
    ),
    ReturnTeam = Table.Column(ReturnRow,returnColumn)   
  in ReturnTeam

My custom column in my Timesheets table:

=fnLookupNameDateMatch([Name],[Date],StaffMovements,"Team")

Am I going about this all wrong?

Thanks for your help.

[tempranello · Accepted solution]

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.

[ImkeF]

Very nice - looks we have a new talent here 🙂

To me this looks like conditional Lookup with partial match.

How about sharing your functions on Github by forking into this one: https://github.com/tycho01/pquery ?

[ImkeF]

Problem is that Table.Max seems to return a record instead of a table. Therefore step ReturnTeam fails, because Table.Column is expecting an input of kind table.

Instead you can use: Record.Field (same syntax)

[sornavoor]

Not an elegant solution - but a work around! ....  Try concatenating name and date and make it a single criterion look up! 

Hope this helps.