Forum Discussion
Power Query custom column based on two criterion
- 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 ReturnResultThanks to all for reading and replying.
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.
- ImkeF10 years agoCommunity Champion
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 ?