• Home
  • About Us
  • Contact Us
  • Privacy Policy
  • Special Offers
Business Intelligence Info
  • Business Intelligence
    • BI News and Info
    • Big Data
    • Mobile and Cloud
    • Self-Service BI
  • CRM
    • CRM News and Info
    • InfusionSoft
    • Microsoft Dynamics CRM
    • NetSuite
    • OnContact
    • Salesforce
    • Workbooks
  • Data Mining
    • Pentaho
    • Sisense
    • Tableau
    • TIBCO Spotfire
  • Data Warehousing
    • DWH News and Info
    • IBM DB2
    • Microsoft SQL Server
    • Oracle
    • Teradata
  • Predictive Analytics
    • FICO
    • KNIME
    • Mathematica
    • Matlab
    • Minitab
    • RapidMiner
    • Revolution
    • SAP
    • SAS/SPSS
  • Humor

Guide – How to import data from Eurostat directly into #PowerBI

January 11, 2019   Self-Service BI

I follow EU Eurostat on twitter (link – https://twitter.com/EU_Eurostat ) and often see a lot of interesting facts and infographics like this one.

And I have for a long time wanted to see if I could use the webservices that Eurostat also provides (link – https://ec.europa.eu/eurostat/data/web-services) to import the data directly into Power BI.

So here is a guide on how you can do it – and the example will try to extract the data for the Orange production Infographic.

There is a LOT of different datasets in Eurostat and this guide should work on most of them – you just need to find the dataset (https://ec.europa.eu/eurostat/data/database) in the catalogue.

Construct the Query

The REST request we need to construct is defined like this

So, we need to find the datasetCode and specify the filters.

You can find the dataset code by browsing the data catalogue – and the dataset code is stated at the end.

 Guide – How to import data from Eurostat directly into #PowerBI

If you need specific items the data explorer you need to specify the code of the items in the request and the Dataexplorer is a good way to find these.

Using the codes we have found we can now use the Query builder to construct our query (click on the picture to try it out)

So after entering the dataset code we get the option to specify the filter and select measures under strucpro

 Guide – How to import data from Eurostat directly into #PowerBI

Notice that I have selected Yes to Exclude EU aggregates

The query option will vary from dataset to dataset but the principles are the same.

Clicking the “Generate query filter” will give you

 Guide – How to import data from Eurostat directly into #PowerBI

And you can copy the dataset code to clipboard

apro_cpsh1?filterNonGeo=1&precision=1&crops=T1000&strucpro=AR&strucpro=PR_HU_EU&time=2014&time=2015&time=2016&time=2017

Now we have the filter part and this can of course be parametrized in your Power Query.

And we must add

http://ec.europa.eu/eurostat/wdds/rest/data/v2.1/json/en/

before so the full web query is

http://ec.europa.eu/eurostat/wdds/rest/data/v2.1/json/en/apro_cpsh1?filterNonGeo=1&precision=1&crops=T1000&strucpro=AR&strucpro=PR_HU_EU&time=2014&time=2015&time=2016&time=2017

In order to get the data into Power BI we choose get data and select the Web connector

 Guide – How to import data from Eurostat directly into #PowerBI

And press OK

This will return the following to the query editor

 Guide – How to import data from Eurostat directly into #PowerBI

Convert the JSON response to tables in Power Query

We get a JSON response returned from the Web query and this has to be transformed into a table – and in order to get that we need to understand what and how the data is returned.

When we use the query in the browser you can see that its not a normal structured JSON document.

 Guide – How to import data from Eurostat directly into #PowerBI

And what we want is a table with the following fields – Country, Year, Area, Production

I start by renaming the query to “QueryResult” and disable the load – and the following query will use this as a reference

So lets create the Geo dimension

 Guide – How to import data from Eurostat directly into #PowerBI

By clicking the Record on the dimension field

 Guide – How to import data from Eurostat directly into #PowerBI

Drill down to geo

 Guide – How to import data from Eurostat directly into #PowerBI

Down to category

 Guide – How to import data from Eurostat directly into #PowerBI

And to label

 Guide – How to import data from Eurostat directly into #PowerBI

Turn it into a table

 Guide – How to import data from Eurostat directly into #PowerBI

And then add an index column starting from 0 and rename the columns

 Guide – How to import data from Eurostat directly into #PowerBI

I then do the same for the other dimensions and end up with the following queries

 Guide – How to import data from Eurostat directly into #PowerBI

Getting the values

Now in order to get the values we need to do a little more

The web query returns a one long list of values when converted into a table.

 Guide – How to import data from Eurostat directly into #PowerBI

Values contains all the “values” in the grid when

 Guide – How to import data from Eurostat directly into #PowerBI

Each cell in the table is referred by a running index starting from 0 to Facts multiplied by Geo by Time.

So when we have 2 facts, 38 countries and 4 years will give us 2 * 39 * 4 = 304 – this should be the number of rows in our Values table.

But when we look at the Values before we do anything we have only 282 rows.

 Guide – How to import data from Eurostat directly into #PowerBI

The missing Values in because cells with missing values (represented by a : or :z ) is located in the Status field in the QueryResult.

 Guide – How to import data from Eurostat directly into #PowerBI

So we have to add the missing rows from this Status – this gives us the missing 22 cells (304-282)

 Guide – How to import data from Eurostat directly into #PowerBI

And we then convert these Values to null

 Guide – How to import data from Eurostat directly into #PowerBI

In our values Query we want to append these rows to our table – and we can do this by modifying the step – by modifying the expression to

= Record.ToTable(value) & MissingRows

 Guide – How to import data from Eurostat directly into #PowerBI

And we rename and change the Name column to Index and change the data type to an integer.

The index column is our number reference to each cell in the table/matrix from EuroStat.

 Guide – How to import data from Eurostat directly into #PowerBI

Next step is to calculate the keys for time, geo and Facts.

To calculate the TimeKey we add a column

 Guide – How to import data from Eurostat directly into #PowerBI

And divide it by the number to time periods

 Guide – How to import data from Eurostat directly into #PowerBI

This gives a step with the following expression

= Table.AddColumn(#”Added Index”, “TimeKey”, each Number.Mod([Index], 4), type number)

And after click OK we can make it more dynamic by modifying the 4 to the number of rows in

= Table.AddColumn(#”Added Index”, “TimeKey”, each Number.Mod([Index], Table.RowCount(Time)), type number)

And now we have the TimeKey for each row.

 Guide – How to import data from Eurostat directly into #PowerBI

To add the FactKey we add another calculated column

= Table.AddColumn(#”Inserted Modulo”, “FactKey”, each Number.IntegerDivide([Index], Table.RowCount(Geo)*Table.RowCount(Time)), Int64.Type)

This will give us the factkey and we can see it shifts when we reach row 152 – time count = 4 and geo count = 38 = 152

 Guide – How to import data from Eurostat directly into #PowerBI

Now we need the final key column is the geoKey

= Table.AddColumn(#”Inserted Integer-Division”, “GeoKey”, each Number.IntegerDivide([Index], Table.RowCount(Time) ) – [FactKey]*Table.RowCount(Geo), Int64.Type)

And we are now ready to load the data into our data model.

 Guide – How to import data from Eurostat directly into #PowerBI

The data is ready to model

After closing the query editor we get the tables returned to Power BI Desktop and we can finalize the datamodel

 Guide – How to import data from Eurostat directly into #PowerBI

And create the relationships between our tables.

And create some measures

 Guide – How to import data from Eurostat directly into #PowerBI

And start to visualize the Oranges in Europe

 Guide – How to import data from Eurostat directly into #PowerBI

One interesting fact is that the Orange trees are highly productive in Albania.

And the cool part is

When 2018 figures is available in EuroStat – we just modify our query to

http://ec.europa.eu/eurostat/wdds/rest/data/v2.1/json/en/apro_cpsh1?filterNonGeo=1&precision=1&crops=T1000&strucpro=AR&strucpro=PR_HU_EU&time=2014&time=2015&time=2016&time=2017&time=2018

and refresh our model.

What do you think?

You can download a copy of the pbix file – here.

Did you like it then give the post a like or a comment – let me know what you think – hope you find it useful.

And at least take a spin around Eurostat to what interesting facts that you can find in the many datasets that are provided to you for free.

Let’s block ads! (Why?)

Erik Svensen – Blog about Power BI, Power Apps, Power Query

#powerbi, data, Directly, Eurostat, from, Guide, Import, into
  • Recent Posts

    • The Easier Way For Banks To Handle Data Security While Working Remotely
    • 3 Ways Data Virtualization is Evolving to Meet Market Demands
    • Did you find everything you need today?
    • Missing Form Editor through command bar in Microsoft Dynamics 365
    • I’m So Excited
  • Categories

  • Archives

    • April 2021
    • March 2021
    • February 2021
    • January 2021
    • December 2020
    • November 2020
    • October 2020
    • September 2020
    • August 2020
    • July 2020
    • June 2020
    • May 2020
    • April 2020
    • March 2020
    • February 2020
    • January 2020
    • December 2019
    • November 2019
    • October 2019
    • September 2019
    • August 2019
    • July 2019
    • June 2019
    • May 2019
    • April 2019
    • March 2019
    • February 2019
    • January 2019
    • December 2018
    • November 2018
    • October 2018
    • September 2018
    • August 2018
    • July 2018
    • June 2018
    • May 2018
    • April 2018
    • March 2018
    • February 2018
    • January 2018
    • December 2017
    • November 2017
    • October 2017
    • September 2017
    • August 2017
    • July 2017
    • June 2017
    • May 2017
    • April 2017
    • March 2017
    • February 2017
    • January 2017
    • December 2016
    • November 2016
    • October 2016
    • September 2016
    • August 2016
    • July 2016
    • June 2016
    • May 2016
    • April 2016
    • March 2016
    • February 2016
    • January 2016
    • December 2015
    • November 2015
    • October 2015
    • September 2015
    • August 2015
    • July 2015
    • June 2015
    • May 2015
    • April 2015
    • March 2015
    • February 2015
    • January 2015
    • December 2014
    • November 2014
© 2021 Business Intelligence Info
Power BI Training | G Com Solutions Limited