Forum Discussion

MMPPCP's avatar
MMPPCP
New Member
1 year ago
Solved

Sequence based item combination with grouping attribute

Dear Community,

 

I would like to ask for your help in a transportation related question.

 

Input table: Transport 1 and Transport 2 with the loading/unloading locations and its sequence

 

Transport IDLoading/unloading sequenceZIP code
Transport 10

1230

Transport 113150
Transport 123204
Transport 133240
Transport 143382
Transport 201230
Transport 214405
Transport 224306
Transport 234858

 

 

Expected output: list of all possible transport relations according to transport IDs (as grouping attributes) and given sequence of the locations - see below. The number of options - in case of 4 stops it is 4+3+2+1 = 10; in case of 3 stops it is 3+2+1 = 6.

 

Transport IDTransport relations based on sequence
Transport 11230_3150
Transport 11230_3204
Transport 11230_3240
Transport 11230_3382
Transport 13150_3204
Transport 13150_3240
Transport 13150_3382
Transport 13204_3240
Transport 13204_3382
Transport 13240_3382
Transport 21230_4405
Transport 21230_4306
Transport 21230_4858
Transport 24405_4306
Transport 24405_4858
Transport 24306_4858

 

If anyone could help, I would be really grateful!

 

Best regards,

Peter

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    Hi MMPPCP -Create a new table in Power BI that lists the transport data as below:

    TransportRelations =

    VAR TransportTable1 =
        SELECTCOLUMNS(
            'transp',
            "TransportID1", 'transp'[Transport ID],
            "Seq1", 'transp'[Loading/unloading sequence],
            "ZIP1", 'transp'[ZIP code]
        )
    VAR TransportTable2 =
        SELECTCOLUMNS(
            'transp',
            "TransportID2", 'transp'[Transport ID],
            "Seq2", 'transp'[Loading/unloading sequence],
            "ZIP2", 'transp'[ZIP code]
        )
    RETURN
        SELECTCOLUMNS(
            FILTER(
                CROSSJOIN(TransportTable1, TransportTable2),
                [TransportID1] = [TransportID2] && [Seq1] < [Seq2]
            ),
            "Transport ID", [TransportID1],
            "Transport relation", [ZIP1] & "_" & [ZIP2]
        )
     

     

    Hope this works 

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi MMPPCP -Create a new table in Power BI that lists the transport data as below:

    TransportRelations =

    VAR TransportTable1 =
        SELECTCOLUMNS(
            'transp',
            "TransportID1", 'transp'[Transport ID],
            "Seq1", 'transp'[Loading/unloading sequence],
            "ZIP1", 'transp'[ZIP code]
        )
    VAR TransportTable2 =
        SELECTCOLUMNS(
            'transp',
            "TransportID2", 'transp'[Transport ID],
            "Seq2", 'transp'[Loading/unloading sequence],
            "ZIP2", 'transp'[ZIP code]
        )
    RETURN
        SELECTCOLUMNS(
            FILTER(
                CROSSJOIN(TransportTable1, TransportTable2),
                [TransportID1] = [TransportID2] && [Seq1] < [Seq2]
            ),
            "Transport ID", [TransportID1],
            "Transport relation", [ZIP1] & "_" & [ZIP2]
        )
     

     

    Hope this works