Get data using API link to OED data

[sebbyp]

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/composite-leading-indicators_da...

How do you tell Power BI to get this infomation?

[lakshayreddy]

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) 

[ImkeF]

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.

[lakshayreddy]

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
ChgType2 

I wish there's a way. Thanks again for the help!😀 

[ImkeF]

Thy have an open API, so you don't need an API key:

let
    Source = Json.Document(Web.Contents("http://stats.oecd.org/SDMX-JSON/data/MEI_CLI/LOLITOAA+LOLITONO+LOLITOTR_STSA+LOLITOTR_GYSA+BSCICP03+CSCICP03+LORSGPRT+LORSGPNO+LORSGPTD+LORSGPOR_IXOBSA.AUS+AUT+BEL+CAN+CHL+CZE+DNK+EST+FIN+FRA+DEU+GRC+HUN+IRL+ISR+ITA+JPN+KOR+LUX+MEX+NLD+NZL+NOR+POL+PRT+SVK+SVN+ESP+SWE+CHE+TUR+GBR+USA+EA19+G4E+G-7+NAFTA+OECDE+OECD+ONM+A5M+BRA+CHN+IND+IDN+RUS+ZAF.M/all?startTime=2015-08&endTime=2017-07&dimensionAtObservation=allDimensions"))
in
    Source

Taken from here:

[sebbyp]

@ImkeFthanks for your help.  The issue i am having is extracting the tables from the API link.  I cannot get the tables in a user friendly format.  Is there a known set of steps to extract the data tables which i can then work with?

[ImkeF · Accepted solution]

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
    ChgType2

Please give a shout if it doesn't!

[Nathorius]

Hi Imke,

Thanks for giving a solution direction. This really helps. I suppose the workaround is still the only way to go.

I am trying to understand how the let / in record to table works if you have a different field structure.

For example, I am trying to get data from an UNHCR api. The basic structure is the same, but the naming and columns are totally different. See: https://api.unhcr.org/population/v1/population/?yearFrom=2010&coo=syr&coa_all=true&cf_type=true&comp..."

Apart from that there is a second issue with this api's, namely that they put multiple values in one row instead of making multiple rows with one value. This example would require a split lines and an extra column to define the different values (asylum type orso). Can that be done while importing in the dataflow too?

Hope someone can share some light on it.

Best, Geert

[Nathorius]

Let me reply to my own question. I almost have it working.

let
    Source = Json.Document(Web.Contents("https://api.unhcr.org/population/v1/population/?yearFrom=2010&coo=syr&coa_all=true&cf_type=true&compress=false")),
    Items = Source[items],
    #"Converted to Table" = Table.FromList(Items, Record.FieldValues)
in
    #"Converted to Table"

This basically does the job. It only misses the table headers. Since I cannot use the first line value as header I have to manually do that.

[sebbyp]

@ImkeFthank you for the code!  I am trying to use it but it is not working at my end.  I think it is something to do with the URL.  Where you have typed URL=> do i just add the web address or is it something a little more than that?

[ImkeF]

Sorry, I thought you were using that already. You have to generate your API-URL (also see pic in 1st post):

[Anonymous]

I'm trying to import data form UIS Statistics (unesco.org) which uses the same system as OECD, but the Developer API tab is no longer an option, there's only SDMX as online export option now.
Do you think there is a way of getting data with a SDMX url? So far I've only managed to import the schema, but I don't see any data.

Thanks!
And sorry for rescueing this thread!

[lakshayreddy]

Hi @Anonymous ,

What i've observed is that only few datasets have Developer API while others don't. I was also looking some trick to get SDMX (XML) query into PowerBI. Please let me know if you've managed to get it done!
Appreciate your effort!😁

[sebbyp]

@ImkeFsorry im not sure what i am doing wrong. I have pasted your query into a blank query and then put the URL into source line item 3 as highlighted in the image but it is not working for me.  What should i do to get it working?

[ImkeF]

There shouldn't be 2 opening parenthesis after WebCall

[sebbyp]

you are a genius.  It is working perfectly!!!  

Thank you so much 

[marianamarques]

Thank you so much for sharing this 🙂

I'm trying to use the query but I'm getting the same error:

[ImkeF]

You're missing the escape signs (quotation marks) for your URL-string: Web.Page( " YourURL " )