Filter table based on another table

[konradjonsson]

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.. ‘.”

[konradjonsson]

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

[watkinnc]

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

[konradjonsson]

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.

[CNENFRNL · Accepted solution]

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'")