Forum Discussion
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+ million rows in it.
Transaction table: InvoiceDetails
I only want to select the rows where a certain category of products are included.
The category information is not included in the transaction table – it is part of a Product table.
Product table: DimProduct
Product table, category field: ProdCodeLvl1
Product table, category field value: ‘3’
Field existing in both tables: WKProduct
let
Source1 = Sql.Databases("server"),
BIDW1 = Source1{[Name="BIDW"]}[Data],
DimProduct1 = BIDW1{[Schema="DW", Item="DimProduct"]}[Data],
Custom1 = DimProduct1,
#"Filtered Rows" = Table.SelectRows(Custom1, each ([ProductCodeLvl1] = "3")),
#"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows",{"WKProduct", "ProductLvl1Name"}),
Source2 = Sql.Databases("server"),
BIDW2 = Source2{[Name="BIDW"]}[Data],
InvoiceDetails = BIDW2{[Schema="DW", Item=" InvoiceDetails"]}[Data],
Custom2 = InvoiceDetails,
#"Filtered Rows2" = Table.SelectRows(Custom2, each Table.Contains(#"Removed Other Columns1","WKProduct"))
in
#"Filtered Rows2"
This setup is resulting in an error message.
“Failed to save modifications to the server. Error returned: ‘OLD DB or ODBC error: [Expression.Error] We cannot convert the value “WKProduct” to type Record.. ‘.”
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'")
4 Replies
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'")Thanks. I am getting help now from more skilled colleagues to do the native query. Once confirmed I have achieved what I wanted - I will accept this as a solution.
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
The solution to this was to use a native query. I got assistance from colleagues that are better skilled with such code. In the end, the code was as follows:
let
Source = Sql.Database("server", "dw", [Query="select #(lf) f.*#(lf)from InvoiceDetails f with (nolock)#(lf)join DW.DimProduct p with (nolock) on p.WKProduct = f.WKProduct#(lf)where #(lf) p.ProductCodeLvl1 = '3'#(lf)"])
in
Source
