Home
Free Trial Sign In



  • Economic Impact
  • Research Techniques
  • Economic Impact Analysis
  • Cost-Benefit Analysis
  • Valuing Social Impact
  • Feasibility and Location Studies
  • Macro and Micro Forecasting
  • Data Analytics and Custom Models
  • Labour Market Analysis
  • Economic Development in EMs
  • State and Local Strategy
  • Scenario Analysis and Stress Testing
  •  
  • Economic Impact archive
  • By Topic
  • Browse Economic Impact by topic
  • By Industry
  • Browse Economic Impact by industry
  • Full archive
  • Publications in date order
  • Thought Leadership
  • Research Inputs
  • Global surveys
  • Executive interviews and case studies
  • Advisory panels and peer interchange
  • Social media research
  • Quantitative and economic analysis
  •  
  • Research Outputs
  • White papers, briefings, and think pieces
  • Charts, infographics, and dashboards
  • Analytical tools and models
  • Social and digital media
  • Microsites and presentations
  • Forward Thinking Blog
  • Click here for latest content
  •  
  • Thought Leadership Examples
  • By topic
  • Publications categorised by Topic
  • By Industry
  • Publications categorised by Industry
  • By Client
  • Publications categorised by Client
  • Full archive
  • Publications in date order

Techlabs

Helpful tips and guidance on

all the good stuff  that comes out of

Oxford Economics Technology Labs

  • Techlabs
  • Using The Databank Api To Load Data Into Power Bi

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.

 

API, Power BI, Databank, Business Intelligence

Search our archive

Search our portfolio of public information
Go to archive
  • Oxford Economics
    • About us
    • Contact us
    • Economists and analysts
    • Careers
  • Products and services
    • Subscription services
    • Economic Impact
    • Thought Leadership
    • Forecasts and scenarios
    • Impact of policy change
  • Help and software
    • Help using our services
    • Software and downloads
    • Latest technology developments
  • Follow Us
    • LinkedIn
    • Twitter
    • Facebook
© Oxford Economics 2021 all rights reserved