Forum Discussion

sebbyp's avatar
sebbyp
Helper III
8 years ago
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...
Need Help
  • ImkeF's avatar
    ImkeF
    8 years ago

    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's avatar
Nathorius
Frequent Visitor
2 years ago

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

Nathorius's avatar
Nathorius
Frequent Visitor
2 years ago

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.