Using the Global Data Workstation API to load data into Power BI

This is a quick step-by-step guide for getting set up with the Oxford Economics API in Microsoft Power BI. If you haven’t already done so, check out our API guide for the Global Data Workstation, which lays out how to access the API, manage selections, and download data.

image

Getting started

1. Start a new Power BI document and select Get Data in the External Data panel.

power-bi-1

2. Select Other > Web in the Get Data window.

power-bi-2

3. Enter the URI for our service, in this case https://services.oxfordeconomics.com.

4. Select the anonymous option for authentication.

power-bi-3

5. Now open up Edit Queries

power-bi-4

6. And then the Advanced Editor.

power-bi-5

7. The final step is to build the request. Below is a sample included in the API examples bundle. See Microsoft's documentation for the M scripting language and our guide on the Global Data Workstation API for setting up valid requests.

In short, the data request is formed using the Web.Contents method, which takes two arguments: the base URL and a list of options. This list should include the relative path to an endpoint, headers, and the POST body when relevant. The source must then be imported as a JSON document. The subsequent code can be generated by Power BI using the query editor.

let
    Source = Web.Contents(
	"https://services.oxfordeconomics.com/api",
	[
		RelativePath="/download?includeMetadata=true",
		Headers=[
			#"Method"="POST",
			#"Content-Type"="application/json",

			// api key must be added below for query to function
			#"Api-Key"=""
		],
		Content=Text.ToBinary("{""Name"":""Simple macro selection"",
					""DatabankCode"": ""WDMacro"",
					""MeasureCode"":""L"",
					""StartYear"":""2016"",
					""EndYear"":""2026"",
					""StackedQuarters"":""false"",
					""Frequency"":""Annual"",
					""Sequence"":""EarliestToLatest"",
					""Precision"":""1"",
					""Order"":""IndicatorLocation"",
					""GroupingMode"":""false"",
					""Regions"": [],
					""Variables"": [{ ""ProductTypeCode"":""WMC"", 
						""VariableCode"":""GDP$"",
						""MeasureCodes"": [""PY""]
					}]
		}")
	]
    ),
    #"Imported JSON" = Json.Document(Source)
in
    #"Imported JSON"

8. Next, the single column of data needs to be turned into a table by right clicking the List header and To Table.
power-bi-6

9. Select the columns and hit OK.
power-bi-7

In some instances, it may be useful to unpivot the results. To do this, select all of the desired columns, right click, and select Unpivot Columns. Otherwise, these are all of the necessary steps for connecting Power BI to the Global Data Workstation API.
power-bi-8

James Mills is a Software Developer at Oxford Economics.
Arvindra Sehmi is Chief Information Officer and Director of IT at Oxford Economics.