Page History

...

Further in this page, there is an example on how the refresh date can be made visible.

In case you need to work , not with the lastest latest data , but a specific data extraction. it is recommended to work with files you downloaded from the API and organised in your storage that can eventually be loaded as CSV File within Excel.

In this case the extraction date could be set as the file modified date as provided by the operating system. Please see below on how to retrieve this information from Excel Power Query

...

It is possible to build an API link to download SDMX-CSV-2.0 data for a dataset via the Databrowser download options menu.

...

This load the Power Query Editor with an initial Source Source step configured

Code Block
= Web.Contents("https://ec.europa.eu/eurostat/api/dissemination/sdmx/3.0/data/dataflow/ESTAT/ISOC_CI_ID_H/1.0?format=csvdata&formatVersion=2.0&compress=false")

...

Info

While this guide try to minimize minimise the need to edit the formula as much as possible, for daily use it is recommended to use compressed input by

removing the &compress=false parameter in the URL
wrapping the Web.Contents into a Binary.Decompress() method for GZIP uncompression

Code Block
= Binary.Decompress(Web.Contents("https://ec.europa.eu/eurostat/api/dissemination/sdmx/3.0/data/dataflow/ESTAT/ISOC_CI_ID_H/1.0?format=csvdata&formatVersion=2.0"),Compression.GZip)

...

Complete result of this step as visible in the Advanced Editor

This code can be copy pasted pasted

Code Block

let
    Source = Binary.Decompress(Web.Contents("https://ec.europa.eu/eurostat/api/dissemination/sdmx/3.0/data/dataflow/ESTAT/ISOC_CI_ID_H/1.0?format=csvdata&formatVersion=2.0"),Compression.GZip),
    #"Imported CSV" = Csv.Document(Source,[Delimiter=",", Columns=10, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"STRUCTURE", type text}, {"STRUCTURE_ID", type text}, {"freq", type text}, {"indic_is", type text}, {"unit", type text}, {"hhtyp", type text}, {"geo", type text}, {"TIME_PERIOD", Int64.Type}, {"OBS_VALUE", Int64.Type}, {"OBS_FLAG", type text}}, "en-150"),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"STRUCTURE", "STRUCTURE_ID"})
in
    #"Removed Columns"

Info

Preparing a query from an example code block in this page

Starting from a blank worksheet, in the Data ribbon menu, in Get Data > Launch Power Query Editor

In New Source menu > Other Sources > Blank Query

Finally opening the Advanced Editor

Image RemovedImage Added=> Image Removed Image Added

Then simply paste an example of a query definition taken from this page to have it loaded into excel.

...

There are 2 options to enable on the initial data query that could enpower empower further data rendering in Excel

...

It is possible to reference content from the data sheet with the standard syntax sheetname!$COLUMN_LETTER$ROW_NUMBER

Image RemovedImage Added

These columns could then be hidden in the data sheet to not hinder data viewing

Image RemovedImage Added

Info

title	Information on Date Time Refreshed

Information about when data was last refreshed is available from the Queries & Connections sidebar as illustrated below

Image RemovedImage Added

Refreshing content can be done from here or from the Data ribbon.

To add the refresh time information to your summary sheet, the best option is to define a new Query with the following setup

Name = Date Time Refreshed
Formula = DateTime.LocalNow()

Image RemovedImage Added

Then load this query into the information sheet you are building

Image RemovedImage Added

Example : building a series key column instead of having one column per dimension code

...

Then select the coded dimension columns and select the Merge Columns in the Transform ribbon and select dot character as separator, this would using using the series key in SDMX REST API calls

Image RemovedImage Added

Image Removed Image Added Image RemovedImage Added

Image RemovedImage Added

This column can be moved by Drag and Drop before the others, this action is recorded as a ReoderColumns ReorderColumns action in the query definition

...

Merge OBS_FLAG column with OBS_VALUE column separated by a space.

Image RemovedImage Added

Select TIME_PERIOD column and "Pivot column", then select previously generated column and advanced option to not aggregate

Image RemovedImage Added

This operation can take a few minutes.

...

It is possible to build an API link to download TSV 2.0 data for a dataset via the Databrowser download options menu.

...

Starting from a blank worksheet, it is possible to load the data for the Data menu with the From Web menu item Image Removeditem Image Added

It opens a window where to enter the URL Image Removed Image Added

This load the Power Query Editor with an initial Source step configured

Code Block
= Web.Contents("https://ec.europa.eu/eurostat/api/dissemination/sdmx/3.0/data/dataflow/ESTAT/ISOC_CI_ID_H/1.0?format=TSV&compress=false ")

Image RemovedImage Added

Info

While this guide try to minimize minimise the need to edit the formula as much as possible, for daily use it is recommended to use compressed input by

removing the &compress=false parameter in the URL
wrapping the Web.Contents into a Binary.Decompress() method for GZIP uncompression

Code Block
= Binary.Decompress(Web.Contents("https://ec.europa.eu/eurostat/api/dissemination/sdmx/3.0/data/dataflow/ESTAT/ISOC_CI_ID_H/1.0?format=TSV"),Compression.GZip)

...

Right click on the file icon named ec.europa.eu and select CSV option Image Removedoption Image Added

This automatically apply the following modification

...

From this point, data is already usable if it is ok to work with a single time series columns, otherwise the first column can be split by comma

Image RemovedImage Added

As you may have noticed Excel did not detect the first line as an header row

...

The Excel workbook can be further customised to your need, the data source can be refreshed via the Refresh button of Excel.

Image RemovedImage Added

Query can be reopened to apply further configuration.

...

Code Block

let
    Source = Csv.Document(Web.Contents("https://ec.europa.eu/eurostat/api/dissemination/sdmx/3.0/data/dataflow/ESTAT/ISOC_CI_ID_H/1.0?format=TSV&compress=false"),[Delimiter="	#(tab)", Columns=11, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"freq", type text}, {"indic_is", type text}, {"unit", type text}, {"hhtyp", type text}, {"geo\TIME_PERIOD", type text}, {"2002 ", type text}, {"2003 ", type text}, {"2004 ", type text}, {"2005 ", type text}, {"2006 ", type text}, {"2007 ", type text}, {"2008 ", type text}, {"2009 ", type text}, {"2010 ", type text}, {"2014 ", type text}},"en-150")
in
    #"Changed Type"

...

Code Block

let
    Source = Csv.Document(Web.Contents("https://ec.europa.eu/eurostat/api/dissemination/sdmx/3.0/data/dataflow/ESTAT/ISOC_CI_ID_H/1.0?format=TSV&compress=false"),[Delimiter="	#(tab)", Columns=11, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"freq", type text}, {"indic_is", type text}, {"unit", type text}, {"hhtyp", type text}, {"geo\TIME_PERIOD", type text}, {"2002 ", type text}, {"2003 ", type text}, {"2004 ", type text}, {"2005 ", type text}, {"2006 ", type text}, {"2007 ", type text}, {"2008 ", type text}, {"2009 ", type text}, {"2010 ", type text}, {"2014 ", type text}},"en-150"),
    #"Split Column by Delimiter 2002" = Table.SplitColumn(#"Changed Type", "2002 ", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"2002.OBS", "2002.FLAG"}),
    #"Changed Type 2002" = Table.TransformColumnTypes(#"Split Column by Delimiter 2002",{{"2002.OBS", type text}, {"2002.FLAG", type text}},"en-150"),
    #"Split Column by Delimiter 2003" = Table.SplitColumn(#"Changed Type 2002", "2003 ", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"2003.OBS", "2003.FLAG"}),
    #"Changed Type 2003" = Table.TransformColumnTypes(#"Split Column by Delimiter 2003",{{"2003.OBS", type text}, {"2003.FLAG", type text}},"en-150"),
    #"Split Column by Delimiter 2004" = Table.SplitColumn(#"Changed Type 2003", "2004 ", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"2004.OBS", "2004.FLAG"}),
    #"Changed Type 2004" = Table.TransformColumnTypes(#"Split Column by Delimiter 2004",{{"2004.OBS", type text}, {"2004.FLAG", type text}},"en-150"),
    #"Split Column by Delimiter 2005" = Table.SplitColumn(#"Changed Type 2004", "2005 ", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"2005.OBS", "2005.FLAG"}),
    #"Changed Type 2005" = Table.TransformColumnTypes(#"Split Column by Delimiter 2005",{{"2005.OBS", type text}, {"2005.FLAG", type text}},"en-150"),
    #"Split Column by Delimiter 2006" = Table.SplitColumn(#"Changed Type 2005", "2006 ", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"2006.OBS", "2006.FLAG"}),
    #"Changed Type 2006" = Table.TransformColumnTypes(#"Split Column by Delimiter 2006",{{"2006.OBS", type text}, {"2006.FLAG", type text}},"en-150"),
    #"Split Column by Delimiter 2007" = Table.SplitColumn(#"Changed Type 2006", "2007 ", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"2007.OBS", "2007.FLAG"}),
    #"Changed Type 2007" = Table.TransformColumnTypes(#"Split Column by Delimiter 2007",{{"2007.OBS", type text}, {"2007.FLAG", type text}},"en-150"),
    #"Split Column by Delimiter 2008" = Table.SplitColumn(#"Changed Type 2007", "2008 ", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"2008.OBS", "2008.FLAG"}),
    #"Changed Type 2008" = Table.TransformColumnTypes(#"Split Column by Delimiter 2008",{{"2008.OBS", type text}, {"2008.FLAG", type text}},"en-150"),
    #"Split Column by Delimiter 2009" = Table.SplitColumn(#"Changed Type 2008", "2009 ", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"2009.OBS", "2009.FLAG"}),
    #"Changed Type 2009" = Table.TransformColumnTypes(#"Split Column by Delimiter 2009",{{"2009.OBS", type text}, {"2009.FLAG", type text}},"en-150"),
    #"Split Column by Delimiter 2010" = Table.SplitColumn(#"Changed Type 2009", "2010 ", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"2010.OBS", "2010.FLAG"}),
    #"Changed Type 2010" = Table.TransformColumnTypes(#"Split Column by Delimiter 2010",{{"2010.OBS", type text}, {"2010.FLAG", type text}},"en-150"),
    #"Split Column by Delimiter 2014" = Table.SplitColumn(#"Changed Type 2010", "2014 ", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"2014.OBS", "2014.FLAG"}),
    #"Changed Type 2014" = Table.TransformColumnTypes(#"Split Column by Delimiter 2014",{{"2014.OBS", type text}, {"2014.FLAG", type text}},"en-150")
in
    #"Changed Type 2014"

...

Recommended way to add labelling on top of TSV data is to load it from the accompanying codelistscode lists. Power Query make this quite easy by allowing to combine several queries

First step is to define several queries to load the data and the necessary codelistscode lists

Query name	Data link
data	https://ec.europa.eu/eurostat/api/dissemination/sdmx/2.1/data/ISOC_CI_ID_H/?format=TSV
FREQ	https://ec.europa.eu/eurostat/api/dissemination/sdmx/2.1/codelist/ESTAT/FREQ?format=TSV
INDIC_IS	https://ec.europa.eu/eurostat/api/dissemination/sdmx/2.1/codelist/ESTAT/INDIC_IS?format=TSV
UNIT	https://ec.europa.eu/eurostat/api/dissemination/sdmx/2.1/codelist/ESTAT/UNIT?format=TSV
HHTYP	https://ec.europa.eu/eurostat/api/dissemination/sdmx/2.1/codelist/ESTAT/HHTYP?format=TSV
GEO	https://ec.europa.eu/eurostat/api/dissemination/sdmx/2.1/codelist/ESTAT/GEO?format=TSV

Then the data table must be combined with the FREQ dimension, the result of this combination must be combined with INDIC_IS dimension etc

To combine two queries, a new query must be created from Power Query Editor and select the column in each table to use to JOIN them. The Join Kind must be Left Outer to execute the lookup properly

Image RemovedImage RemovedImage RemovedImage Added Image Added Image Added

This will add a new column at the end of the table that should be edited to only display Column 2 of the codelist content to render the labels.

Image RemovedImage Added

When all labels columns are created they can be moved to be next to the code column

All these steps are summarized summarised in the following workbook and codeblockcode sample

View file

name	TSV-ISOC.xlsx
page	API - FAQ - Usage in Excel Power Query
space	EUROSTATHELP
height	250

Code Block

// data
let
    Source = Table.FromColumns({Lines.FromBinary(Web.Contents("https://ec.europa.eu/eurostat/api/dissemination/sdmx/2.1/data/ISOC_CI_ID_H/?format=TSV"), null, null, 65001)}),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByAnyDelimiter({",", "#(tab)"}, QuoteStyle.Csv)),
    #"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true])
in
    #"Promoted Headers"

// FREQ
let
    Source = Csv.Document(Web.Contents("https://ec.europa.eu/eurostat/api/dissemination/sdmx/2.1/codelist/ESTAT/FREQ?format=TSV"),[Delimiter="	#(tab)", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}},"en-150")
in
    #"Changed Type"

// INDIC_IS
let
    Source = Csv.Document(Web.Contents("https://ec.europa.eu/eurostat/api/dissemination/sdmx/2.1/codelist/ESTAT/INDIC_IS?format=TSV"),[Delimiter="	#(tab)", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}},"en-150")
in
    #"Changed Type"

// UNIT
let
    Source = Csv.Document(Web.Contents("https://ec.europa.eu/eurostat/api/dissemination/sdmx/2.1/codelist/ESTAT/UNIT?format=TSV"),[Delimiter="	#(tab)", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}},"en-150")
in
    #"Changed Type"

// HHTYP
let
    Source = Csv.Document(Web.Contents("https://ec.europa.eu/eurostat/api/dissemination/sdmx/2.1/codelist/ESTAT/HHTYP?format=TSV"),[Delimiter="	#(tab)", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}},"en-150")
in
    #"Changed Type"

// GEO
let
    Source = Csv.Document(Web.Contents("https://ec.europa.eu/eurostat/api/dissemination/sdmx/2.1/codelist/ESTAT/GEO?format=TSV"),[Delimiter="	#(tab)", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}},"en-150")
in
    #"Changed Type"

// data+FREQ
let
    Source = Table.NestedJoin(data, {"freq"}, FREQ, {"Column1"}, "FREQ.1", JoinKind.LeftOuter),
    #"Expanded FREQ.1" = Table.ExpandTableColumn(Source, "FREQ.1", {"Column2"}, {"FREQ.1.Column2"})
in
    #"Expanded FREQ.1"

// data+FREQ+INDIC_IS
let
    Source = Table.NestedJoin(#"data+FREQ", {"indic_is"}, INDIC_IS, {"Column1"}, "INDIC_IS.1", JoinKind.LeftOuter),
    #"Expanded INDIC_IS.1" = Table.ExpandTableColumn(Source, "INDIC_IS.1", {"Column2"}, {"INDIC_IS.1.Column2"})
in
    #"Expanded INDIC_IS.1"

// data+FREQ+INDIC_IS+UNIT
let
    Source = Table.NestedJoin(#"data+FREQ+INDIC_IS", {"unit"}, UNIT, {"Column1"}, "UNIT.1", JoinKind.LeftOuter),
    #"Expanded UNIT.1" = Table.ExpandTableColumn(Source, "UNIT.1", {"Column2"}, {"UNIT.1.Column2"})
in
    #"Expanded UNIT.1"

// data+FREQ+INDIC_IS+UNIT+HHTYP
let
    Source = Table.NestedJoin(#"data+FREQ+INDIC_IS+UNIT", {"hhtyp"}, HHTYP, {"Column1"}, "HHTYP.1", JoinKind.LeftOuter),
    #"Expanded HHTYP.1" = Table.ExpandTableColumn(Source, "HHTYP.1", {"Column2"}, {"HHTYP.1.Column2"})
in
    #"Expanded HHTYP.1"

// data+LABELS
let
    Source = Table.NestedJoin(#"data+FREQ+INDIC_IS+UNIT+HHTYP", {"geo\TIME_PERIOD"}, GEO, {"Column1"}, "GEO", JoinKind.LeftOuter),
    #"Expanded GEO" = Table.ExpandTableColumn(Source, "GEO", {"Column2"}, {"GEO.Column2"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded GEO",{"freq", "FREQ.1.Column2", "indic_is", "INDIC_IS.1.Column2", "unit", "UNIT.1.Column2", "hhtyp", "HHTYP.1.Column2", "geo\TIME_PERIOD", "GEO.Column2", "2002 ", "2003 ", "2004 ", "2005 ", "2006 ", "2007 ", "2008 ", "2009 ", "2010 ", "2014 "})
in
    #"Reordered Columns"

Tip

title	Share data queries

Option 1 : Copy several queries between workbooks and share workbook files

Starting from a blank worksheet, it is possible to re-use existing queries from another workbook

Image RemovedImage Added

Create a new worksheet

Reveal the Queries & Connections Image RemovedConnections Image Added

Right-click under the queries pane and click Paste Image Removed Image Added

This will automatically load the last query into the current worksheet

Option 2 : Export and share ODC files

When a query is completed, it is possible to select the final query to be exported as an ODC file as the one below

View file

name	TSV-ISOC-WITH-LABELS.odc	page	API - FAQ - Usage in Excel Power Query
space	EUROSTATHELP
height	250

This file can be simply double-clicked on windows and should open in Excel directly.

Otherwise it can be loaded as an existing connection

Image RemovedImage Added

This menu automatically list connections present in the "My Data Sources" user folder C:\Users\user_id\Documents\My Data Sources

Files in another location can be opened with the Browser Browse for more more button.

More information on ODC files from Microsoft documentation

...

Code Block

let
    Source = Json.Document(Web.Contents("https://ec.europa.eu/eurostat/api/dissemination/sdmx/3.0/structure/dataflow/ESTAT/ISOC_CI_ID_H?format=JSON&lang=en&compress=false")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Pivoted Column" = Table.Pivot(#"Converted to Table", List.Distinct(#"Converted to Table"[Name]), "Name", "Value"),
    #"Expanded extension" = Table.ExpandRecordColumn(#"Pivoted Column", "extension", {"lang", "id", "agencyId", "version", "datastructure", "annotation"}, {"extension.lang", "extension.id", "extension.agencyId", "extension.version", "extension.datastructure", "extension.annotation"}),
    #"Expanded extension.annotation" = Table.ExpandListColumn(#"Expanded extension", "extension.annotation"),
    #"Expanded extension.annotation1" = Table.ExpandRecordColumn(#"Expanded extension.annotation", "extension.annotation", {"type", "title", "date", "href"}, {"extension.annotation.type", "extension.annotation.title", "extension.annotation.date", "extension.annotation.href"})
in
    #"Expanded extension.annotation1"

Image RemovedImage Added

Child pages

Versions Compared

Old Version 2

New Version Current

Key

Example : building a series key column instead of having one column per dimension code

Option 1 : Copy several queries between workbooks and share workbook files

Option 2 : Export and share ODC files