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
ChgType2Please give a shout if it doesn't!
