Hello there,
I have a query that presents all invoices in the current year to date. I would like to filter this data by customer, using a separate list of our top 20 customers by spend.
I have tried using the following, where tTopCustomers is my separate tabulated list of top 20 customers and [CUSTOMER] is the column in the invoice data that I wish to filter:
= Table.SelectRows(#"Reordered Columns", each Table.Contains(tTopCustomers, [CUSTOMER]))
But this only results in an expression error along the lines of
"We cannot convert the value "*CUSTOMERNAME*" to type Record.
Details:
Value=*CUSTOMERNAME*
Type=Type"
First of all, am I using a valid function to achieve the desired filter and if so, how do I correct the error?
Also, I had originally tried to use List.Contains, however I'm unable to find a way to convert the table query to a list - is this possible in excel?
Thanks for your help 🙂
Hi Alex,
Welcome to our forum.
You can do this with a merge. See file attached. I've done a 'merge as new', but you could replace the tInvoices with a merge on that table.
Mynda
Thank you, Mynda - often the simple answer is staring us right in the face!
