Forum Discussion

magloons's avatar
magloons
Regular Visitor
1 year ago
Solved

Merge two tables with no usable identifiers

The problem: I have two tables. One only contains the Truck ID and each Article on the truck and the other table contains only the delivery date and the article number expected to be delivered on tha...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    1 year ago

    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"

     

     

     

v-sshirivolu's avatar
v-sshirivolu
Community Support
1 year ago

 

Hi magloons ,
Thanks for reaching out to the Microsoft fabric community forum.

Follow these steps to achieve your desired result.
Construct Grouped Article Lists -
Create the TruckArticles table :

TruckArticles =

SUMMARIZE(
   TableA,
   TableA[Truck_ID],
   "Articles", CONCATENATEX(
       VALUES(TableA[Article_Number]),
       TableA[Article_Number],
       ",",
       TableA[Article_Number],
       ASC
   )
)


Create the DateArticles table:

DateArticles = 
SUMMARIZE(
   TableB,
   TableB[Delivery_Date],
   "Articles", CONCATENATEX(
       VALUES(TableB[Article_Number]),
       TableB[Article_Number],
       ",",
       TableB[Article_Number],
       ASC
   )
)

 

Create Matching Table - 

TruckDateMapping table:

TruckDateMapping =
SELECTCOLUMNS(
   FILTER(
       CROSSJOIN(TruckArticles, DateArticles),
       TruckArticles[Articles] = DateArticles[Articles]
   ),
   "Truck_ID", TruckArticles[Truck_ID],
   "Delivery_Date", DateArticles[Delivery_Date]
)

 

To display the results, use a Table visual and include the following fields:

TruckDateMapping[Truck_ID]

TruckDateMapping[Delivery_Date]


Please find the attached .pbix file for your reference.

Regards,
Sreeteja.

  • v-sshirivolu's avatar
    v-sshirivolu
    Community Support
    1 year ago

    Hi magloons ,

    I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

     

    • v-sshirivolu's avatar
      v-sshirivolu
      Community Support
      1 year ago

      Hi magloons ,
      I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you

       

      • v-sshirivolu's avatar
        v-sshirivolu
        Community Support
        1 year ago

        Hi magloons ,
        I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.