ABS Data API Example - Draw Trade data into Excel Using PowerQuery

Here are some steps to create the worksheet utilising Power Query:

  1. Open a new excel worksheet
  2. On the Data tab select From Web
  3. Select advanced and specify an API call for the data of interest
    URL part 1 is the endpoint to dataset “https://api.data.abs.gov.au/data/MERCH_EXP/
    URL part 2 querying data - order of dimensions can be found via Data Explorer - for all Exports to
    China by SITC this could be “.CHIN.TOT.M”
    HTTP header added to return the file as CSV including labels - “Accept”: “application/vnd.sdmx.data+csv;labels=both”
  4. Once you select OK Power Query should open, right-click the result and specify that the content is CSV
  5. Now you should see the actual data returned and can start applying transformations to get it in the clients desired format
  6. In case annotations are possible (no data and instead comment such as break in time series) first merge OBS_VALUE, OBS_STATUS and OBS_COMMENT columns, by selecting all 3 and either right-click > merge or merge columns from the Transform tab
    Generally only 1 will be present so it is OK to ignore the separator
  7. Remove constant columns (DATAFLOW, COUNTRY_DEST, FREQ, etc), select and right-click > Remove Columns or Remove Columns in the Home tab
    This will allow pivoting time against SITC as we now just have COMMODITY_SITC, TIME_PERIOD and Observation
  8. Choose Pivot Column from the Transform tab and select TIME_PERIOD, making sure not to aggregate the Observations via Advanced
  9. Split COMMODITY_SITC into codes and labels via Split Column by Delimiter on the Transform tab, choosing the Left-most Colon
  10. This split adds a type change of the code to numbers which we should remove from the applied steps on the right, especially with codes that start with 0
  11. Optional step) If we want the total of commodities at the top transform the TOT code to -1
    Find TOT and right-click > Replace Values, Replace With -1
  12. Now we can sort ascending on the SITC codes via the column drop down
  13. Transform the -1 code back to TOT with right-click > Replace Values again
  14. Double click on the column names to rename as you like
  15. A lot more transformations to be applied as desired, such as filtering Code to length < 3 as was provided in 1 tab to NSW Treasury
  16. Once transformation is finished, from the Home tab select Close & Load
  17. You should now have your data table in the desired format and the ability to update with right-click > Refresh
  18. To go back and edit the query or add more steps, select Queries & Connections from the Excel Data tab to get the right side panel and then right-click > Edit the Query
  19. With the right side Query Settings panel allowing you to edit the name and view the different steps applied

Example file will be added when I work out how

Example Excel file using Power Query to request Merchandise Exports to China, created using the instructions above.

Power Query example - Merchandise Exports from Australia to China.xlsx (139.7 KB)