Table merge in Power Query based on conditions

[matherhorn64]

Hi Guys,

what I'm looking for is the conditional merge in Power Query.

Here is the situation I'm in - there are 3 tables with historical data:

1. Product pricing table, it looks like this - it is the matrix of product combinations, prices for which they are being sold and dates when the pricing was active
   
   
2. Gross margin table - looks exactly like the Product pricing table but with different value inside
   
3. Sales table - shows each Major Product + Sub product (if any) sold for each date
   

All tables are loaded to power query. What I need to do is to add a column to Sales table with the Gross margin from Gross margin table. It implies that Power query needs to pick up the Gross margin value from Gross margin table based on the condition that sales date is between Priced from and Priced to dates. Is there any way of doing this advanced merge in Power query?

What I came up with is very "excel" way of doing it which has little to do with advanced Power query tools:

1. Pivot columns in Gross margin table so that it shows Major Product and Sub-product as only 2 separate columns
2. Create a table of unique Pricing ranges (Priced From and Priced To) from Gross margin table and concatenate the price ranges (From And To)
3. Sort sales data table ascending and do the left outer merge ON distinct values From Date from Gross margin table -> bring the concatenated values (Priced From & Priced To)
4. Fill down columns with the concatenated values from Gross Margin table in Sales data table -> now we know for each sales date what was the pricing period and where we should look for the gross margin
5. Create unique columns in Sales Table and Gross margin table (by concatenating Major Product & Sub Product & Priced From & Priced To -> do the table merge -> bring the needed value of Gross margin

I don't really my approach because all the tables are really heavy (couple of millions of rows) + I'm doing additional operations on them -> all that leads to really slow Power Query performance. I assume that optimisation could be done with advanced Power Query merge (I know for sure it can be done via SQL).

What do you think?

[Anonymous · Accepted solution]

Hi @matherhorn64

Assuming you want to retrieve the gross margin for every sale, you could try this function "fn_Lookup_GrossMargin".

(LookupProduct as text, LookupSubproduct as text, Lookupdate as date) =>

let
    Source = Gross_margin,
    Comparison = Table.SelectRows(Source, each [Priced From] <= Lookupdate and [Priced To]>= Lookupdate and [Product] = LookupProduct),
    Result = if Table.RowCount(Comparison) <> 1 then null else Record.Field(Comparison{0},LookupSubproduct)
in
    Result

in your Sales Table, add a new column:

= Table.AddColumn(#"Changed Type", "GrossMargin", each fn_Lookup_GrossMargin([Major Product],[Sub Product],[Date]), Int64.Type)

Hope this helps.

[matherhorn64]

Thanks very much! It works.

[Anonymous]

(LookupProduct as text, LookupSubproduct as text, Lookupdate as date)

 Hey @matherhorn64 and @Anonymous !

I'm just starting out, could someone please explain the function in slighly more detail especially what the above line does and how/where to create the function?

Thanks!!