...
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
|
...
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" |
...
There are 2 options to enable on the initial data query that could enpower empower further data rendering in Excel
...
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
...
It is possible to build an API link to download TSV 2.0 data for a dataset via the Databrowser download options menu.
...
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
|
...
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
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
...
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 | ||||
---|---|---|---|---|
|
...
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 | ||||||
---|---|---|---|---|---|---|
| ||||||
Option 1 : Copy several queries between workbooks and share workbook filesStarting from a blank worksheet, it is possible to re-use existing queries from another workbook Create a new worksheet Reveal the Queries & Connections Right-click under the queries pane and click Paste This will automatically load the last query into the current worksheet Option 2 : Export and share ODC filesWhen a query is completed, it is possible to select the final query to be exported as an ODC file as the one below
This file can be simply double-clicked on windows and should open in Excel directly. Otherwise it can be loaded as an existing connection 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. |
...