Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
sebbyp
Helper III
Helper III

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

 

How do you tell Power BI to get this infomation?

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

18 REPLIES 18
Saurabh6919
New Member

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.

 

Saurabh6919_0-1733418573616.png

 

lakshayreddy
Frequent Visitor

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.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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 

 

Error.png

 

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

ImkeF
Super User
Super User

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:

OECD_API.jpg

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@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?

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!

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

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.

 

2024-04-12_17-19-45.png

@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?

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

 

 

OECD_API2.jpg

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

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.
unesco sdmx.JPG
Thanks!
And sorry for rescueing this thread!

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

Capture.PNG

 

@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?

There shouldn't be 2 opening parenthesis after WebCall

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

 

Thank you so much 

 

Smiley Happy

Thank you so much for sharing this 🙂

 

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

 

PBI_query.JPG

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.