Forum Discussion
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 T...
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])RETURNSELECTCOLUMNS(FILTER(CROSSJOIN(TransportTable1, TransportTable2),[TransportID1] = [TransportID2] && [Seq1] < [Seq2]),"Transport ID", [TransportID1],"Transport relation", [ZIP1] & "_" & [ZIP2])Hope this works
rajendraongole1
Super User
Super UserHi 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
