Json to Excel

Transforming

The shipment list can be downloaded in raw json format. This json can be converted to excel using the following steps:

1. Download the file and store the json in a file, e.g. shipmentlist.json

2. Open Excel and go to the Data tab

3. Click on Get Data and select From File and then From JSON

4. Select the file shipmentlist.json and click Import

5. Right click on the white part of the _value record and select Drill Down

6. Right-mouse-click on the List header and click To Table

7. Use the defaults for the To Table dialog and click OK

8. Use the Expand button to expand the columns

   1. Keep all columns selected (or remove the ones you don’t need), press “OK”

9. For each of the columns that contains an object

• you can recognize this if the value of the cell is Record

• if it is not an object, the actual text of the value is shown in the cell

• click on the expand button and select the columns you want to expand, for example each address block: - Choose the fields

  

  • This results in the fields in the table:

  

10. For handlingUnits, there is a one-to-many relationship with the shipment. If you wish to include handling unit information (weight, dimensions, references), this can be expanded by clicking the expand button and selecting “Expand to New Rows”. This will create a new row for each handling unit in the shipment, while repeating the shipment information:
    
11. View list in Excel using the Close & Load button on the left top corner of the Home panel
    This results in the table visible in Excel:

Undo steps

When you are still in the ‘wizzard’ of the Power Query Editor:

In case you make a mistake, you can undo the last step by right-mouse-clicking on the last step in the Applied Steps pane and selecting Delete:

When you are in the Excel sheet:

Doubleclick on the ShipmentList in the Queries & Connections pane on the right side of the Excel sheet. This will open the Power Query Editor, follow the same steps as above to undo a step.