Forum Discussion
Solved
Table Joins with two conditions
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:
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"
2 Replies
- mwegener
Most Valuable Professional
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"Works fine!
Thank you
