Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Can somebody help with API access. I would like to access the economic data tables provided by the OECD. Here is the link
How do you tell Power BI to get this infomation?
Solved! Go to 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
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
I wish there's a way. Thanks again for the help!😀
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:
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.
@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):
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
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!
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!😁
@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
Thank you so much for sharing this 🙂
I'm trying to use the query but I'm getting the same error:
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
93 | |
88 | |
35 | |
35 |
User | Count |
---|---|
154 | |
101 | |
80 | |
63 | |
54 |