Forum Discussion

konradjonsson's avatar
konradjonsson
Helper II
5 years ago
Solved

Filter table based on another table

Hi.   I need help with a solution to filter a large transaction table. I want to this already during load in Power Query. Not sure it is possible.   I have a table with transactions. It has 50+ m...
Need Help
Show and Tell
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    Syntax for Table.Contains

    https://docs.microsoft.com/en-us/powerquery-m/table-contains

     

    As to me, another solution way much better is to execute such a join query at database side,

    Source = Sql.Databases("server"),
Dataset = Value.NativeQuery(Source, "SELECT * FROM DimProduct1 AS Prdt INNER JOIN InvoiceDetails AS Inv ON Prdt.WKProduct = Inv.WKProduct WHERE ProductCodeLvl1 = '3'")
Anonymous's avatar
Anonymous
Not applicable
5 years ago

I would change the Table.Contains to List.Contains, like:

#"Filltered Rows2" = Table.SelectRows(Custom2, each List.Contains(List.Buffer(#"Removed Other Columns1"[WKProduct]),[ColumnNameInCustom2ThatYouAreFiltering]))

This way you are filtering Custom2 table with a buffered list of the values from the WKProduct column of your filtered table. And it'll fold.

--Nate