March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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:
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:
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?
Solved! Go to Solution.
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.
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.
Thanks very much! It works.
(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!!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
167 | |
147 | |
92 | |
70 | |
58 |