...
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 => Then simply paste an example of a query definition taken from this page to have it loaded into excel. |
...
It is possible to reference content from the data sheet with the standard syntax sheetname!$COLUMN_LETTER$ROW_NUMBER
These columns could then be hidden in the data sheet to not hinder data viewing
Info | ||
---|---|---|
| ||
Information about when data was last refreshed is available from the Queries & Connections sidebar as illustrated below 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
Then load this query into the information sheet you are building |
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
This column can be moved by Drag and Drop before the others, this action is recorded as a ReoderColumns action in the query definition
...
Merge OBS_FLAG column with OBS_VALUE column separated by a space.
Select TIME_PERIOD column and "Pivot column", then select previously generated column and advanced option to not aggregate
This operation can take a few minutes.
...
Starting from a blank worksheet, it is possible to load the data for the Data menu with the From Web menu item item
It opens a window where to enter the URL
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 ") |
Info | ||
---|---|---|
While this guide try to minimize the need to edit the formula as much as possible, for daily use it is recommended to use compressed input by
|
...
Right click on the file icon named ec.europa.eu and select CSV option option
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
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.
Query can be reopened to apply further configuration.
...
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
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.
When all labels columns are created they can be moved to be next to the code column
...
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=" ", 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=" ", 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=" ", 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=" ", 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=" ", 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 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 for 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" |