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 that date.  

I want to somehow merge these in order to make a table where I can see the delivery date for each Truck ID. Now imagine that the datasets are much larger then this and some identical articles are on multiple trucks. How could one do that?

One idea I had was to group by TruckID and make a comma delimited cell with all article numbers in ascending order for each Truck_ID.
Then the same Group By on the other table just with the articles grouped on the given dates. 

I can do that part but I cant figure out a way to find the date that best correlates to each Truck ID based on the article groups.. I think im overcomplicating this.. If it can be done in DAX I am open to that of course.

Fictitous example below:

Table A

Truck_ID                 Article_Number
PTR-1004230412345
PTR-1004230454321
PTR-1004230498851
PTR-1004230487123
PTR-1004230473329
GSU-12410051054321
GSU-12410051012345
GSU-12410051098794
GSU-12410051063265
GSU-12410051087921

 

Table B

Delivery_Date  Article_Number
20.09.202512345
20.09.202554321
20.09.202598851
20.09.202587123
20.09.202573329
08.08.202554321
08.08.202512345
08.08.202598794
08.08.202563265
08.08.202587921
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"

     

     

     

10 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    1 year ago

    What do you expect for a result generated from the data you have presented?

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    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.

    • magloons's avatar
      magloons
      Regular Visitor
      1 year ago

      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.

      • ronrsnfld's avatar
        ronrsnfld
        Super User
        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