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 download

  2. Open Excel and go to the Data tab

  3. Click on Get Data and select From File and then From JSON get data from file

  4. Select the file shipmentlist.json and click Import

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

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

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

  8. Use the Expand button to expand the columns

    1. Keep all columns selected (or remove the ones you don’t need), press “OK” expand columns
  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: expand address

    • Choose the fields

    expanded address

    • This results in the fields in the table:

    address in table

  1. 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:
    expand handling units
  2. View list in Excel using the Close & Load button on the left top corner of the Home panel load close button
    This results in the table visible in Excel: shipment list 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:

undo mistakes

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.

how to go to power query editor