Forum Discussion
Merge two tables with no usable identifiers
For some reason, you have chosen to not show what you expect for results from the data you have presented.
However, examination of your data shows that the articles are listed in exactly the same order in both tables. If that is truly the case and relevant, all you need to do is add the Delivery_Date column to your Table A. (If that is not the case, you will need to provide what I previously requested along with your logic in developing your result table)
let //Read in the data tables SourceA = Excel.CurrentWorkbook(){[Name="TableA"]}[Content], TableA = Table.TransformColumnTypes(SourceA,{{"Truck_ID", type text}, {"Article_Number", Int64.Type}}), SourceB = Excel.CurrentWorkbook(){[Name="TableB"]}[Content], TableB = Table.TransformColumnTypes(SourceB,{{"Delivery_Date", type date}, {"Article_Number", Int64.Type}}), //Add the delivery date column #"Add Delivery Date" = Table.FromColumns( Table.ToColumns(TableA) & {TableB[Delivery_Date]}, type table[Truck_ID=text, Article_Number=Int64.Type, Delivery_Date=date]) in #"Add Delivery Date"
In your upstream system add the truck id to the table B. Most likely you will need a Delivery ID too as trucks can make multiple deliveries per day.
I dont have access to the upstream system sadly. These tables are what I have for now. But I do think the dataset is large enough that I could identify the most likely delivery date per TruckID based on the mix of articles on it.
For some reason, you have chosen to not show what you expect for results from the data you have presented.
However, examination of your data shows that the articles are listed in exactly the same order in both tables. If that is truly the case and relevant, all you need to do is add the Delivery_Date column to your Table A. (If that is not the case, you will need to provide what I previously requested along with your logic in developing your result table)
let //Read in the data tables SourceA = Excel.CurrentWorkbook(){[Name="TableA"]}[Content], TableA = Table.TransformColumnTypes(SourceA,{{"Truck_ID", type text}, {"Article_Number", Int64.Type}}), SourceB = Excel.CurrentWorkbook(){[Name="TableB"]}[Content], TableB = Table.TransformColumnTypes(SourceB,{{"Delivery_Date", type date}, {"Article_Number", Int64.Type}}), //Add the delivery date column #"Add Delivery Date" = Table.FromColumns( Table.ToColumns(TableA) & {TableB[Delivery_Date]}, type table[Truck_ID=text, Article_Number=Int64.Type, Delivery_Date=date]) in #"Add Delivery Date"
