Table Joins with two conditions

[DagosKev]

I have two tables in Power Query:

Sales_table> sales per day

Cost_Table> Product costs by date

I can't find a way to bring the cost of the product (Cost_Table) to my sales table (Sales_Table).

The conditions should be:

Sales_Table.ITEM = Cost_Table.ITEM

Sales_Table.DATE> = Cost_Table.DATE

The result should be the following:

[mwegener · Accepted solution]

Hi @DagosKev 

check this:

// COST_TABLE
let
    Source = Excel.Workbook(File.Contents("C:\SALES_COST.xlsx"), null, true),
    COST_TABLE_Table = Source{[Item="COST_TABLE",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(COST_TABLE_Table,{{"DATE(DD/MM/YYYY)", type date}, {"ITEM", type text}, {"COST", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ITEM", Order.Ascending}, {"DATE(DD/MM/YYYY)", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"ITEM", "Index.1"}, #"Added Index1", {"ITEM", "Index"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"DATE(DD/MM/YYYY)"}, {"DATE(DD/MM/YYYY).1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Added Index1",{{"DATE(DD/MM/YYYY)", "Startdate"}, {"DATE(DD/MM/YYYY).1", "Enddate"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Index", "Index.1"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,#date(9999, 12, 31),Replacer.ReplaceValue,{"Enddate"})
in
    #"Replaced Value"

// SALES_TABLE
let
    Source = Excel.Workbook(File.Contents("C:\SALES_COST.xlsx"), null, true),
    SALES_TABLE_Table = Source{[Item="SALES_TABLE",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(SALES_TABLE_Table,{{"SALEID", Int64.Type}, {"ITEM", type text}, {"QTY", Int64.Type}, {"UNITPRICE", Int64.Type}, {"DATE(DD/MM/YYYY)", type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ITEM"}, COST_TABLE, {"ITEM"}, "COST_TABLE", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "COST_ROW", each Table.SelectRows([COST_TABLE], (CostRow) =>  _[#"DATE(DD/MM/YYYY)"] >= CostRow[Startdate] and _[#"DATE(DD/MM/YYYY)"] < CostRow[Enddate])),
    #"Expanded COST_ROW" = Table.ExpandTableColumn(#"Added Custom", "COST_ROW", {"COST"}, {"COST"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded COST_ROW",{"COST_TABLE"})
in
    #"Removed Columns"

[DagosKev]

Works fine!

Thank you