topic Re: Get data using API link to OED data in Desktop

Get data using API link to OED data

sebbyp — Wed, 30 Aug 2017 16:18:55 GMT

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_data-00042-en?isPartOf=/content/datacollection/mei-data-en

How do you tell Power BI to get this infomation?

Re: Get data using API link to OED data

ImkeF — Thu, 31 Aug 2017 08:03:47 GMT

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:

Re: Get data using API link to OED data

sebbyp — Thu, 31 Aug 2017 09:33:46 GMT

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?

Re: Get data using API link to OED data

ImkeF — Thu, 31 Aug 2017 18:50:03 GMT

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!

Re: Get data using API link to OED data

sebbyp — Fri, 01 Sep 2017 07:31:56 GMT

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?

Re: Get data using API link to OED data

ImkeF — Fri, 01 Sep 2017 07:38:53 GMT

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

Re: Get data using API link to OED data

sebbyp — Fri, 01 Sep 2017 07:46:24 GMT

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?

Re: Get data using API link to OED data

ImkeF — Fri, 01 Sep 2017 07:49:22 GMT

There shouldn't be 2 opening parenthesis after WebCall

Re: Get data using API link to OED data

sebbyp — Fri, 01 Sep 2017 07:53:35 GMT

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

Thank you so much

:smileyhappy:

Re: Get data using API link to OED data

marianamarques — Wed, 02 May 2018 18:54:28 GMT

Thank you so much for sharing this :)

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

Re: Get data using API link to OED data

ImkeF — Wed, 02 May 2018 19:17:20 GMT

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

Re: Get data using API link to OED data

Anonymous — Fri, 05 Mar 2021 16:15:50 GMT

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!

Re: Get data using API link to OED data

lakshayreddy — Fri, 18 Jun 2021 14:17:29 GMT

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)

Re: Get data using API link to OED data

lakshayreddy — Fri, 18 Jun 2021 14:20:17 GMT

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!😁

Re: Get data using API link to OED data

ImkeF — Sat, 19 Jun 2021 06:17:44 GMT

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.

Re: Get data using API link to OED data

lakshayreddy — Sat, 19 Jun 2021 10:56:30 GMT

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!😀

Re: Get data using API link to OED data

Nathorius — Fri, 12 Apr 2024 08:26:36 GMT

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&compress=false"

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

Re: Get data using API link to OED data

Nathorius — Fri, 12 Apr 2024 15:24:39 GMT

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.

Re: Get data using API link to OED data

Saurabh6919 — Thu, 05 Dec 2024 17:10:00 GMT

Hello ImkeF ,

I using the API I'm getting below error, I'm trying to download the Economic outlook data. Please help me how can I resolve the below error.