...
| 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" |
...
| 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" |
...