Forum Discussion
Solved
Get data using API link to OED data
Can somebody help with API access. I would like to access the economic data tables provided by the OECD. Here is the link http://www.oecd-ilibrary.org/economics/data/main-economic-indicators/co...
This function should work for all OECD-datasets via the JSON-API in Flat Format:
(URL) => let fnConvertRecordList = (Recordlist) => Table.ExpandRecordColumn(Table.FromList(Recordlist, Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Column1", Record.FieldNames(Record.Combine(Table.FromList(Recordlist, Splitter.SplitByNothing(), null, null, ExtraValues.Error)[Column1]))), fnAddKeyToList = (list) => Table.AddIndexColumn(Table.FromColumns({list}), "Key",0,1), Source = Json.Document(Web.Contents(URL)), ToTable = Table.FromRecords({Source}), Expand = Table.ExpandRecordColumn(ToTable, "structure", {"links", "name", "description", "dimensions", "attributes", "annotations"}, {"links", "name", "description", "dimensions", "attributes", "annotations"}), Attr = fnConvertRecordList(Expand[attributes]{0}[observation]), Dim = fnConvertRecordList(Expand[dimensions]{0}[observation]), Dims = Table.Combine({Attr,Dim}), #"Added Index1" = Table.AddIndexColumn(Dims, "Index", 1, 1), AddKeyToValues = Table.AddColumn(#"Added Index1", "Custom", each fnAddKeyToList([values])), ExpandValues = Table.ExpandTableColumn(AddKeyToValues, "Custom", {"Column1", "Key"}, {"Column1", "Key"}), FilterNotNull = Table.SelectRows(ExpandValues, each ([Key] <> null)), LookupTbl = Table.ExpandRecordColumn(FilterNotNull, "Column1", {"id", "name"}, {"id.1", "name.1"}), Datasets = Expand[dataSets]{0}{0}[observations], ConvertToTable = Record.ToTable(Datasets), AddKeys = Table.AddColumn(ConvertToTable, "Custom", each fnAddKeyToList(List.Combine({[Value],Text.Split([Name], ":")}))), Cleanup = Table.RemoveColumns(AddKeys,{"Value"}), ExpandValues2 = Table.ExpandTableColumn(Cleanup, "Custom", {"Column1", "Key"}, {"Value", "Key"}), ChgType = Table.TransformColumnTypes(ExpandValues2,{{"Value", type number}}), Amount = Table.AddColumn(ChgType, "Amount", each if [Key]=0 then [Value] else null), FillDownAmount = Table.FillDown(Amount,{"Amount"}), #"Filtered Rows" = Table.SelectRows(FillDownAmount, each ([Value] <> null) and ([Key] <> 0)), MergeLookup = Table.NestedJoin(#"Filtered Rows",{"Key", "Value"},LookupTbl,{"Index", "Key"},"Expanded Custom",JoinKind.LeftOuter), ExpandLookup = Table.ExpandTableColumn(MergeLookup, "Expanded Custom", {"id", "id.1"}, {"id", "id.1"}), Cleanup3 = Table.RemoveColumns(ExpandLookup,{"Key", "Value"}), Pivot = Table.Pivot(Cleanup3, List.Distinct(Cleanup3[id]), "id", "id.1"), MergeLookup2 = Table.NestedJoin(Pivot,{"Name"},MergeLookup,{"Name"},"Pivoted Column",JoinKind.LeftOuter), Expand2 = Table.ExpandTableColumn(MergeLookup2, "Pivoted Column", {"Expanded Custom"}, {"Expanded Custom"}), Expand3 = Table.ExpandTableColumn(Expand2, "Expanded Custom", {"name", "name.1"}, {"name.2", "name.1"}), AddSpace = Table.TransformColumns(Expand3,{{"name.2", each _&" "}}), Pivot2 = Table.Pivot(AddSpace, List.Distinct(AddSpace[name.2]), "name.2", "name.1"), Cleanup4 = Table.RemoveColumns(Pivot2,{"Name"}), ChgType2 = Table.TransformColumnTypes(Cleanup4,{{"Amount", type number}}) in ChgType2Please give a shout if it doesn't!
Thanks for the Developer API conversion Trick ImkeF 😀
I am working on pulling data from this other dataset but it didnt have a Developer API option. It only had SDMX (XML) export available. Is there a workaround available for that? Please let me your thoughts.
Example: Analytical House Price Indicators (https://www.oecd-ilibrary.org/economics/data/prices/analytical-house-price-indicators_cbcc2905-en)
Hey folks,
I think they moved to a general API-page: API Documentation (oecd.org)
So to query the house prices from 2020 and 2021 your query would look like so:Json.Document(Web.Contents("http://stats.oecd.org/sdmx-json/data/HOUSE_PRICES?startTime=2020&endTime=2021"))
XML-Specs can be found here: sdmx-ml-documentation (oecd.org)
Query would be so:Xml.Tables(Web.Contents("https://stats.oecd.org/restsdmx/sdmx.ashx/GetData/HOUSE_PRICES?startTime=2020&endTime=2021"))Looks like you can retrieve all data with this method.
Please let me know how it worked out.Heyy ImkeF ,
I tried using this function after changing the Json.Document(Web.Contents(URL)) to Xml.Tables(Web.Contents(URL)). But, I seem to get an error. Can you please tell me where I'm going wrong?
I basically want to import SDMX-XML typye OECD datasets into PowerBI. The function for Developer API aka JSON.Document is working fine.
let
fnConvertRecordList = (Recordlist) => Table.ExpandRecordColumn(Table.FromList(Recordlist, Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Column1", Record.FieldNames(Record.Combine(Table.FromList(Recordlist, Splitter.SplitByNothing(), null, null, ExtraValues.Error)[Column1]))),
fnAddKeyToList = (list) => Table.AddIndexColumn(Table.FromColumns({list}), "Key",0,1),
Source = Xml.Tables(Web.Contents("https://stats.oecd.org/restsdmx/sdmx.ashx/GetData/HOUSE_PRICES?startTime=2020&endTime=2021")),
ToTable = Table.FromRecords({Source}),
Expand = Table.ExpandRecordColumn(ToTable, "structure", {"links", "name", "description", "dimensions", "attributes", "annotations"}, {"links", "name", "description", "dimensions", "attributes", "annotations"}),
Attr = fnConvertRecordList(Expand[attributes]{0}[observation]),
Dim = fnConvertRecordList(Expand[dimensions]{0}[observation]),
Dims = Table.Combine({Attr,Dim}),
#"Added Index1" = Table.AddIndexColumn(Dims, "Index", 1, 1),
AddKeyToValues = Table.AddColumn(#"Added Index1", "Custom", each fnAddKeyToList([values])),
ExpandValues = Table.ExpandTableColumn(AddKeyToValues, "Custom", {"Column1", "Key"}, {"Column1", "Key"}),
FilterNotNull = Table.SelectRows(ExpandValues, each ([Key] <> null)),
LookupTbl = Table.ExpandRecordColumn(FilterNotNull, "Column1", {"id", "name"}, {"id.1", "name.1"}),
Datasets = Expand[dataSets]{0}{0}[observations],
ConvertToTable = Record.ToTable(Datasets),
AddKeys = Table.AddColumn(ConvertToTable, "Custom", each fnAddKeyToList(List.Combine({[Value],Text.Split([Name], ":")}))),
Cleanup = Table.RemoveColumns(AddKeys,{"Value"}),
ExpandValues2 = Table.ExpandTableColumn(Cleanup, "Custom", {"Column1", "Key"}, {"Value", "Key"}),
ChgType = Table.TransformColumnTypes(ExpandValues2,{{"Value", type number}}),
Amount = Table.AddColumn(ChgType, "Amount", each if [Key]=0 then [Value] else null),
FillDownAmount = Table.FillDown(Amount,{"Amount"}),
#"Filtered Rows" = Table.SelectRows(FillDownAmount, each ([Value] <> null) and ([Key] <> 0)),
MergeLookup = Table.NestedJoin(#"Filtered Rows",{"Key", "Value"},LookupTbl,{"Index", "Key"},"Expanded Custom",JoinKind.LeftOuter),
ExpandLookup = Table.ExpandTableColumn(MergeLookup, "Expanded Custom", {"id", "id.1"}, {"id", "id.1"}),
Cleanup3 = Table.RemoveColumns(ExpandLookup,{"Key", "Value"}),
Pivot = Table.Pivot(Cleanup3, List.Distinct(Cleanup3[id]), "id", "id.1"),
MergeLookup2 = Table.NestedJoin(Pivot,{"Name"},MergeLookup,{"Name"},"Pivoted Column",JoinKind.LeftOuter),
Expand2 = Table.ExpandTableColumn(MergeLookup2, "Pivoted Column", {"Expanded Custom"}, {"Expanded Custom"}),
Expand3 = Table.ExpandTableColumn(Expand2, "Expanded Custom", {"name", "name.1"}, {"name.2", "name.1"}),
AddSpace = Table.TransformColumns(Expand3,{{"name.2", each _&" "}}),
Pivot2 = Table.Pivot(AddSpace, List.Distinct(AddSpace[name.2]), "name.2", "name.1"),
Cleanup4 = Table.RemoveColumns(Pivot2,{"Name"}),
ChgType2 = Table.TransformColumnTypes(Cleanup4,{{"Amount", type number}})
in
ChgType2I wish there's a way. Thanks again for the help!😀