Forum Discussion

Aggro's avatar
Aggro
Frequent Visitor
2 years ago
Solved

Power Query Filter Data (List.Contains)

I'm using List Contains function from power query to filter the data that I need. 

I have a transaction list, transaction_list (in all text format), and a data table called data_table. 

When I use list contains to add a condition to the filtering process, it just doesn't work right. Keep giving me an error saying:Expression.Error: We cannot convert a value of type List to type Text.

 

FilteredTable = Table.SelectRows(Source, each
if Text.Start([transaction_list], 3) = "22-" then
List.Contains(transaction_list, [buyer_transaction_id])
else if Text.Start([transaction_list], 3) = "55-" then
List.Contains(transaction_list, [sell_transaction_id])
else
List.Contains(transaction_list, [transaction_id])

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Aggro ,

     

    You may consider using the List.AnyTrue() function to minimize the number of List.Contains calls.

    each List.AnyTrue({
    List.Contains(transaction_list, [transaction_id]),
    List.Contains(transaction_list, [sell_transaction_id]),
    List.Contains(transaction_list, [buyer_transaction_id])
})

    List.AnyTrue() will only check for the first condition that is met, and once a match is found, it will stop checking the remaining ones, which can speed up the execution.

     

    Best Regards,

    Bof

3 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Text.Start needs a text parameter passed to it. It's getting the list passed to it.  What are you trying to do?

    • Aggro's avatar
      Aggro
      Frequent Visitor
      2 years ago

      i'm searching for data from the transaction list.

      Normally I would do:

       

      each (List.Contains(transaction_list,[transaction_id])) or List.Contains(transaction_list,[sell_transaction_id])) or  List.Contains(transaction_list,[buyer_transaction_id]))

       

      but it would take super long to filter out data. 

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi Aggro ,

         

        You may consider using the List.AnyTrue() function to minimize the number of List.Contains calls.

        each List.AnyTrue({
    List.Contains(transaction_list, [transaction_id]),
    List.Contains(transaction_list, [sell_transaction_id]),
    List.Contains(transaction_list, [buyer_transaction_id])
})

        List.AnyTrue() will only check for the first condition that is met, and once a match is found, it will stop checking the remaining ones, which can speed up the execution.

         

        Best Regards,

        Bof