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: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 activeGross margin table - looks exactly like the Product pricing table but with different value insideSales table - shows each Major Product + Sub product (if any) sold for each dateAll 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:Pivot columns in Gross margin table so that it shows Major Product and Sub-product as only 2 separate columnsCreate a table of unique Pricing ranges (Priced From and Priced To) from Gross margin table and concatenate the price ranges (From And To)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)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 marginCreate 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 marginI 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?

Hi matherhorn64Assuming 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 Resultin 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

Frequent Visitor

9 years ago

Solved

Table merge in Power Query based on conditions

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:

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
Gross margin table - looks exactly like the Product pricing table but with different value inside
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:

Pivot columns in Gross margin table so that it shows Major Product and Sub-product as only 2 separate columns
Create a table of unique Pricing ranges (Priced From and Priced To) from Gross margin table and concatenate the price ranges (From And To)
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)
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
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?

Need Help

Anonymous

9 years ago

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.

Forum Discussion

Table merge in Power Query based on conditions

3 Replies

Related content

Merge Summarize Table

Conditional Merge

Multiple table merge with multiple column condition

Merge table with a condition with power query

Snowflake Tables Not Merging in Service