Forum Discussion

dnsia's avatar
dnsia
Helper II
5 years ago
Solved

How to insert a conditional row

Hi all, 

 

I am trying to calculate the total number of containers loaded for each vessel / voyage but it is not showing the number of containers that came from transshipment (airline term: layover). 

 


I have 4 datasets for this. 

 

1. Volume- This lists only the direct shipment ( in airline term, direct flight) but not w/ transshipment (layover).

BL Number EQPID Vessel Voyage Bound Load Port Discharge Port
BL01 Container1 VES1 1 N PORT1 PORT3

 

2. Load -  all the containers that are confirmed to have departed the origin (PORT1) and with the final destination (PORT3).

BL Number Container_No Load Port Discharge Port Vessel Voyage Bound
BL01 Container1 PORT1 PORT3 VES1 1 N

 

3. Transshipment (Layover) - list the container being discharged and loaded to another vessel to reach the final destination port. 

BL Number Container_No Load Transhipment Discharge Transhipment VSLCode VoyCode Bound
BL01 CONTAINER1 PORT2   VES2 2 S
BL01 CONTAINER1   PORT2 VES1 1 N


4. Destination - Lists all containers that have confirmed to reach the final destination (PORT3).

BL Number Discharge Port Container_No Vessel Voyage Bound Load Port
BL01 PORT3 CONTAINER1 VES2 2 S PORT1



Desired Output is for Table 1 to be like below

 

BL NumberEQPID Vessel Voyage Bound Load Port Discharge Port Load Transhipment Discharge Transhipment
BL01Container1 VES1 1 N PORT1 PORT3 NULL PORT 2
BL01Container1 VES2 2 S PORT1 PORT3 PORT2 NULL

 

 


Thanks a lot in advance for your help! 



Regards,

Dina

Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi  dnsia  ,

    Here are the steps you can follow：

    1. Create calculated table.

    Table =
GENERATEALL(
    'Transshipment (Layover)',
    var _table1BL='Transshipment (Layover)'[BL Number]
    return
    SELECTCOLUMNS(
        CALCULATETABLE('Destination','Destination'[BL Number]=_table1BL),
        "Load port",'Destination'[Load Port]))
    Table 2 =
GENERATEALL(
    'Table',
    var _table1BL='Table'[BL Number]
    return
    SELECTCOLUMNS(
        CALCULATETABLE('Volume','Volume'[BL Number]=_table1BL),
        "Discharge Port",'Volume'[Discharge Port]))

    2. Result：

    You can downloaded PBIX file from here

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

38 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi  dnsia  ,

    Here are the steps you can follow：

    1. Create calculated table.

    Table =
GENERATEALL(
    'Transshipment (Layover)',
    var _table1BL='Transshipment (Layover)'[BL Number]
    return
    SELECTCOLUMNS(
        CALCULATETABLE('Destination','Destination'[BL Number]=_table1BL),
        "Load port",'Destination'[Load Port]))
    Table 2 =
GENERATEALL(
    'Table',
    var _table1BL='Table'[BL Number]
    return
    SELECTCOLUMNS(
        CALCULATETABLE('Volume','Volume'[BL Number]=_table1BL),
        "Discharge Port",'Volume'[Discharge Port]))

    2. Result：

    You can downloaded PBIX file from here

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • PC2790's avatar
    PC2790
    Community Champion
    5 years ago

    Hello dnsia 

     

    You can consider merging the desired tables in Power Query to get the required fields from the another one.

    This might help

     

    Try if this helps