Here are some steps to create the worksheet utilising Power Query:
- Open a new excel worksheet
- On the Data tab select From Web
- 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” - Once you select OK Power Query should open, right-click the result and specify that the content is CSV
- Now you should see the actual data returned and can start applying transformations to get it in the clients desired format
- 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 - 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 - Choose Pivot Column from the Transform tab and select TIME_PERIOD, making sure not to aggregate the Observations via Advanced
- Split COMMODITY_SITC into codes and labels via Split Column by Delimiter on the Transform tab, choosing the Left-most Colon
- 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
- 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 - Now we can sort ascending on the SITC codes via the column drop down
- Transform the -1 code back to TOT with right-click > Replace Values again
- Double click on the column names to rename as you like
- 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
- Once transformation is finished, from the Home tab select Close & Load
- You should now have your data table in the desired format and the ability to update with right-click > Refresh
- 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
- 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