How to get data from UNESCO online (SDMX)?

[Anonymous]

I am trying to import data from the new UNESCO data platform: UIS Statistics (unesco.org)

I want an online connection, and for that they only offer SDMX:

I've tried several approaches and I get to see the data schema, but I haven't managed to get the data.

I found these two workarounds but I am not sure it's the same case because the OED also offers the Developer API export:

https://community.powerbi.com/t5/Desktop/Get-data-using-API-link-to-OED-data/td-p/242333

https://community.powerbi.com/t5/Desktop/Import-Data-from-OECD-website/td-p/496287

Any help would be much appreciated!

[ImkeF · Accepted solution]

Hi @Anonymous ,

it's hidden in the navigation pane.
You have to expand the folder in order for being able to select the dataset:

[ImkeF]

Hello @Anonymous ,

yes, that's due to the format itself. It's basically a multidimensional format, so you have to parse it out manually.

[ImkeF · Accepted solution]

Hi @Anonymous ,

it's hidden in the navigation pane.
You have to expand the folder in order for being able to select the dataset:

[Anonymous]

Thanks @ImkeF, but I still get to a step where the data seems to be splitted in multiple tables:

I've come this far:

let
    Source = Xml.Tables(Web.Contents("http://data.uis.unesco.org/RestSDMX/sdmx.ashx/GetData/NATMON_DS/OFST_1_CP+OFST_1_F_CP+OFST_1_M_CP+OFST_AGM1_CP+OFST_AGM1_F_CP+OFST_AGM1_M_CP.AFG+ALA+ALB+DZA+ASM+AND+AGO+AIA+ATG+ARG+ARM+ABW+AUS+AUT+AZE+BHS+BHR+BGD+BRB+BLR+BEL+BLZ+BEN+BMU+BTN+BOL+BIH+BWA+BRA+VGB+BRN+BGR+BFA+BDI+KHM+CMR+CAN+CPV+CYM+CAF+TCD+ZZA+CHL+CHN+HKG+MAC+COL+COM+COG+COK+CRI+CIV+HRV+CUB+CUW+CYP+CZE+PRK+COD+DNK+DJI+DMA+DOM+ECU+EGY+SLV+GNQ+ERI+EST+SWZ+ETH+FRO+FLK+FJI+FIN+FRA+GUF+PYF+GAB+GMB+GEO+DEU+GHA+GIB+GRC+GRL+GRD+GLP+GUM+GTM+GGY+GIN+GNB+GUY+HTI+VAT+HND+HUN+ISL+IND+IDN+IRN+IRQ+IRL+IMN+ISR+ITA+JAM+JPN+JEY+JOR+KAZ+KEN+KIR+KWT+KGZ+LAO+LVA+LBN+LSO+LBR+LBY+LIE+LTU+LUX+MDG+MWI+MYS+MDV+MLI+MLT+MHL+MTQ+MRT+MUS+MYT+MEX+FSM+MCO+MNG+MNE+MSR+MAR+MOZ+MMR+NAM+NRU+NPL+NLD+ANT+NCL+NZL+NIC+NER+NGA+NIU+NFK+MKD+MNP+NOR+OMN+PAK+PLW+PSE+PAN+PNG+PRY+PER+PHL+PCN+POL+PRT+PRI+QAT+KOR+MDA+REU+ROU+RUS+RWA+SHN+KNA+LCA+SPM+VCT+BLM+MAF+WSM+SMR+STP+SAU+SEN+SRB+SYC+SLE+SGP+SXM+SVK+SVN+SLB+SOM+ZAF+SSD+ESP+LKA+SDN+XDN+SUR+SJM+SWE+CHE+SYR+TJK+THA+TLS+TGO+TKL+TON+TTO+TUN+TUR+TKM+TCA+TUV+UGA+UKR+ARE+GBR+TZA+USA+VIR+URY+UZB+VUT+VEN+VNM+WLF+ESH+YEM+ZMB+ZWE+SDG+40675+40330+40334+40344+40606+40617+40603+40614+40618+40612+40616+40619+40611+40613+40630+40650+40651+40656+40642+40620+40640+UIS+40510+40525+40530+40505+40515+40520+40500+40535+40540+40550+WB+40044+40042+40030+40043+40041/all?startTime=2014&endTime=2020")),
    Table1 = Source{1}[Table],
    Table0 = Table1{0}[Table],
    #"Changed Type1" = Table.TransformColumnTypes(Table0,{{"KeyFamilyRef", type text}, {"Attribute:keyFamilyURI", type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type1",{{"KeyFamilyRef", type text}, {"Attribute:keyFamilyURI", type text}}),
    Series = #"Changed Type"{0}[Series]
in
    Series

[v-yingjl · Accepted solution]

Hi @Anonymous ,

It depends on your dataset, you need to expand the table to extract the corresponding data in power query.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.