Sequence based item combination with grouping attribute

[MMPPCP]

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 ID	Loading/unloading sequence	ZIP code
Transport 1	0	1230
Transport 1	1	3150
Transport 1	2	3204
Transport 1	3	3240
Transport 1	4	3382
Transport 2	0	1230
Transport 2	1	4405
Transport 2	2	4306
Transport 2	3	4858

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 ID	Transport relations based on sequence
Transport 1	1230_3150
Transport 1	1230_3204
Transport 1	1230_3240
Transport 1	1230_3382
Transport 1	3150_3204
Transport 1	3150_3240
Transport 1	3150_3382
Transport 1	3204_3240
Transport 1	3204_3382
Transport 1	3240_3382
Transport 2	1230_4405
Transport 2	1230_4306
Transport 2	1230_4858
Transport 2	4405_4306
Transport 2	4405_4858
Transport 2	4306_4858

If anyone could help, I would be really grateful!

Best regards,

Peter

[rajendraongole1 · Accepted solution]

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 

[MMPPCP]

Dear @rajendraongole1 ,

thank you very much, it works perfect.

Best regards,

Peter