Forum Discussion

matherhorn64's avatar
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...
Need Help
  • Anonymous's avatar
    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.

     

Anonymous's avatar
Anonymous
Not applicable
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.

 

  • matherhorn64's avatar
    matherhorn64
    Frequent Visitor
    9 years ago

    Thanks very much! It works.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      (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!!