Skip to content
September 15, 2014 / Erik Svensen

Specifying JSON Query in Power Query – Example Statistics Sweden

Not all data API provides you with the same help as Statistics Denmark (see a previous blog post link) where you get help to construct the URL to retrieve the data via a GET Request (http://api.statbank.dk/console#data)

In another project, I was interested in retrieving data about Sweden – and quickly found Statistics Sweden – http://www.scb.se/en_/. The site has a lot of interesting data and facts about Sweden and they also provide access the the data via and API (http://www.scb.se/en_/About-us/Open-data-API/)

Unfortunately the Swedes doesn’t provide the same help as their Danish colleagues, so we have to use a little Power Query magic to retrieve the data – J

Here is how its done.

 

If you click the example link to retrieve the population for 2011 and 2012 – http://api.scb.se/OV0104/v1/doris/en/ssd/BE/BE0101/BE0101A/BefolkningNy – you will get a JSON response but this will only contain the variables that can be used to query the result and no data.

So I started to search the documentation and found that in order to get the data you have to create a GET request to the API with a JSON Query to get data.

But how can we do this in Power Query.

The Help on Web.Contents() in Power Query doesn’t provide much help besides the remark about POST/GET under the content option field (http://office.microsoft.com/en-us/excel-help/web-contents-HA104112310.aspx?CTT=5&origin=HA104122363 – but I found the inspiration/solution via Chris Webb’s blogpost http://cwebbbi.wordpress.com/2014/04/19/web-services-and-post-requests-in-power-query/ – if I could specify the JSON Query as the Content it should be possible.

So lets try

First on the PowerQuery tab select “From Web” and insert the address – http://api.scb.se/OV0104/v1/doris/en/ssd

 

This will give you a list of all the different variables you can set via the API, but not the data

In order to specify the JSON Query we have to switch to the advanced editor and modify the PowerQuery.

First we need a variable that holds the JSON Query – but remember to replace the ” with “” as the text in the query has to be stated properly.

Then we can use the content variable in the call to the Web.Contents –by adding “, [Content=Text.ToBinary(content)]))” to call.

When we click ok – we get the data J

Then we navigate the JSON result by expanding the list, convert to a table and expand the different columns in the query.

 

The result is now a bunch of keys for the years and regions in Sweden. We could then create other queries to the API in order to get these key values but there is actually an easier way. We can ask the API to return the data returned by JSON query as CSV instead of JSON, and remember to modify the Source to only web.contents and not a Json.Document.

Example to retrieve the population for Sweden in 2013 split by region and ages

 

let

PostContents= “{

“”query””: [

{

“”code””: “”Kon””,

“”selection””: {

“”filter””: “”item””,

“”values””: [

“”1″”,

“”2″”

]

}

},

{

“”code””: “”Alder””,

“”selection””: {

“”filter””: “”all””,

“”values””: [

“”*””

]

}

},

{

“”code””: “”ContentsCode””,

“”selection””: {

“”filter””: “”item””,

“”values””: [

“”BE0101N1″”

]

}

},

{

“”code””: “”Tid””,

“”selection””: {

“”filter””: “”item””,

“”values””: [

“”2013″”

]

}

}

],

“”response””: {

“”format””: “”csv””

}

}

“,

Source = Web.Contents(“http://api.scb.se/OV0104/v1/doris/sv/ssd/START/BE/BE0101/BE0101A/BefolkningNy”,[Content=Text.ToBinary(PostContents)]),

#”Imported CSV” = Csv.Document(Source,null,”,”,null,1252),

#”Removed Top Rows” = Table.Skip(#”Imported CSV”,1),

#”Renamed Columns” = Table.RenameColumns(#”Removed Top Rows”,{{“Column1”, “RegionKey”}, {“Column2”, “Age”}, {“Column3”, “Gender”}, {“Column4”, “Population”}}),

#”Split Column by Delimiter” = Table.SplitColumn(#”Renamed Columns”,”RegionKey”,Splitter.SplitTextByEachDelimiter({” “}, null, false),{“RegionKey.1”, “RegionKey.2”}),

#”Inserted Text Length” = Table.AddColumn(#”Split Column by Delimiter”, “Length”, each Text.Length([RegionKey.1]), type number),

#”Filtered Rows” = Table.SelectRows(#”Inserted Text Length”, each ([Length] = 4)),

#”Renamed Columns1″ = Table.RenameColumns(#”Filtered Rows”,{{“RegionKey.1”, “RegionKey”}, {“RegionKey.2”, “RegionName”}}),

#”Replaced Value” = Table.ReplaceValue(#”Renamed Columns1″,” år”,””,Replacer.ReplaceText,{“Age”}),

#”Replaced Value1″ = Table.ReplaceValue(#”Replaced Value”,”+”,””,Replacer.ReplaceText,{“Age”}),

#”Filtered Rows1″ = Table.SelectRows(#”Replaced Value1″, each ([Age] <> “totalt ålder”)),

#”Replaced Value2″ = Table.ReplaceValue(#”Filtered Rows1″,” “,””,Replacer.ReplaceText,{“Population”}),

#”Changed Type” = Table.TransformColumnTypes(#”Replaced Value2″,{{“Population”, Int64.Type}, {“Age”, Int64.Type}}),

#”Inserted Custom” = Table.AddColumn(#”Changed Type”, “EndOfYear”, each Date.From(“2013/12/31”)),

#”Removed Columns” = Table.RemoveColumns(#”Inserted Custom”,{“Length”}),

#”Filtered Rows2″ = Table.SelectRows(#”Removed Columns”, each ([Population] <> 0)),

#”Changed Type1″ = Table.TransformColumnTypes(#”Filtered Rows2″,{{“EndOfYear”, type date}})

in

#”Changed Type1″

 

This result can be returned to the datamodel

And we can start using PowerView, PowerMap to visualize the population.

OBS – As the API returns data with Swedish number format – you have to change the Power Query local to Swedish (Sweden) in order for the Query not to fail.

 

And then we can start analyzing the data in a pivottable

 

Or in PowerView in Excel

 

Here is the scary scenario for men when turning 70 …. Looks very dangerous. And Power Map lets us plot by county easily.

 

Or use the new PowerMap filter function to create a heatmap of the regions with 0-3 year old

 

If you sell diapers or sell children’s shoes in Sweden, you can see where to focus.

You can download a copy of the workbook – from here, and let me know if you have questions or like it.

 

 

 

Advertisements

7 Comments

Leave a Comment
  1. kent.soendergaard@mdlz.com / Sep 16 2014 8:23 am

    Hej Erik,

    Håber alt er vel!

    Fantastisk blogg – den vil jeg hygge meg med en stille lørdag aften over et glas rødvin! – Men et par spørgsmål upfront!

    Hvilken version af excel skal jeg have for at kunne følge din blog? Vi har office 10 på Mdlz… Kan jeg klare mig med det – eller skal jeg stjele mig tid på min datters pc?
    Power Pivot / Power Query – er det samme sag? Eller to forskellige add-in’s?

    Mvh Kent

    • Erik Svensen / Sep 16 2014 8:56 am

      Hej Kent,

      Godt at høre at du kan lide bloggen og den kan gøre lørdagshyggen optimal 🙂

      Du har Power Pivot og Power Query i Excel 2010, men du vil ikke kunne lave alle eksemplerne – da du hverken har Power Map eller Power View I Excel 2010.

      Power Pivot og Power Query er 2 forskellige add-ins som du kan downloade fra Microsoft.

      Lad mig endelig høre hvis du har brug for hjælp

      Mange hilsner
      Erik

  2. Ola / Oct 26 2014 11:06 pm

    Excellent!

  3. Joshua Chung / Oct 29 2015 7:17 pm

    Hi. I’ve actually tried this exact code before, but the error message that I get back from the web service is

    (415): UNSUPPORTED MEDIA TYPE
    Details:
    DataSourceKind=Web

    My source code looks like this below

    let
    PostContents = “{ “”interval_unit””: “”day””, “”start_at””: “”2015-07-01T00:00:00″”, “”series””: [ { “”metrics””: [ “”facebook:follows”” ], “”filter””: “”license:22180:channel:17640312″”, “”type””: “”channel””, “”label””: “”facebook”” } ], “”interval””: 1, “”end_at””: “”2015-10-03T00:00:00″”, “”timezone””: null, “”api_key””: “”MyAPIKeyWouldGoHere”” }”,
    PercolateResponse = Web.Contents(“https://URLBlocked.com/api/v4/analytics/”, [Content=Text.ToBinary(PostContents)]),
    Source = Json.Document(PercolateResponse)
    in
    Source

    Am I missing anything here? Any help would be appreciated. Thank you.

    • Erik Svensen / Nov 6 2015 7:47 am

      Hi Joshua,

      It looks like you are trying to get data from Facebook – right ?

      If so have you tried the Power Query Facebook connector – I have used that in other cases and have had case where the “Unsupported Media Type” was shown in some columns with error values.

      Hope this can help you a Little bit.

      br
      Erik

  4. Ola / Jul 18 2016 3:41 am

    Thanks for the Tip!
    //Ola.S

    Just 4 fun, this also works…

    Add this Excel-Table:
    Row, Start/End, Code, Selection, Filter, Values
    1 {“query”:[
    2 {“code”:”Kon”, “selection”:{ “filter”:”item”, “values”:[“1″,”2”]}},
    3 {“code”:”Alder”, “selection”:{ “filter”:”all”, “values”:[“*”]}},
    4 {“code”:”ContentsCode”, “selection”:{ “filter”:”item”, “values”:[“BE0101N1”]}},
    5 {“code”:”Tid”, “selection”:{ “filter”:”item”, “values”:[“2013″]}}
    6 ],”response”:{“format”:”csv”}}

    let
    Source1 = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
    #”Changed Type” = Table.TransformColumnTypes(Source1,{{“Row”, Int64.Type}, {“Start/End”, type text}, {“Code”, type text}, {“Selection”, type text}, {“Filter”, type text}, {“Values”, type text}}),
    #”Removed Columns” = Table.RemoveColumns(#”Changed Type”,{“Row”}),
    #”Merged Columns” = Table.CombineColumns(#”Removed Columns”,{“Start/End”, “Code”, “Selection”, “Filter”, “Values”},Combiner.CombineTextByDelimiter(“”, QuoteStyle.None),”Merged”),
    #”Transposed Table” = Table.Transpose(#”Merged Columns”),
    #”Merged Columns1″ = Table.CombineColumns(#”Transposed Table”,{“Column1”, “Column2”, “Column3”, “Column4”, “Column5”, “Column6”},Combiner.CombineTextByDelimiter(“”, QuoteStyle.None),”JSON_Script”),
    #”Replaced Value” = Table.ReplaceValue(#”Merged Columns1″,””””,””””””,Replacer.ReplaceText,{“JSON_Script”}),
    #”Replaced Value1″ = Table.ReplaceValue(#”Replaced Value”,”{“”””query””””:”,”””{“”””query””””:”,Replacer.ReplaceText,{“JSON_Script”}),
    #”Replaced Value2″ = Table.ReplaceValue(#”Replaced Value1″,”:””””csv””””}}”,”:””””csv””””}}”””,Replacer.ReplaceText,{“JSON_Script”}),
    JSON_Script1 = Expression.Evaluate(#”Replaced Value2″{0}[JSON_Script]),

    Source = Web.Contents(“http://api.scb.se/OV0104/v1/doris/sv/ssd/START/BE/BE0101/BE0101A/BefolkningNy”,[Content=Text.ToBinary(JSON_Script1)]),
    #”Imported CSV” = Csv.Document(Source,null,”,”,null,1252),
    #”Removed Top Rows” = Table.Skip(#”Imported CSV”,1),
    #”Renamed Columns” = Table.RenameColumns(#”Removed Top Rows”,{{“Column1”, “RegionKey”}, {“Column2”, “Age”}, {“Column3”, “Gender”}, {“Column4”, “Population”}}),
    #”Split Column by Delimiter” = Table.SplitColumn(#”Renamed Columns”,”RegionKey”,Splitter.SplitTextByEachDelimiter({” “}, null, false),{“RegionKey.1”, “RegionKey.2″}),
    #”Inserted Text Length” = Table.AddColumn(#”Split Column by Delimiter”, “Length”, each Text.Length([RegionKey.1]), type number),
    #”Filtered Rows” = Table.SelectRows(#”Inserted Text Length”, each ([Length] = 4)),
    #”Renamed Columns1″ = Table.RenameColumns(#”Filtered Rows”,{{“RegionKey.1”, “RegionKey”}, {“RegionKey.2”, “RegionName”}}),
    #”Replaced Value3″ = Table.ReplaceValue(#”Renamed Columns1″,” år”,””,Replacer.ReplaceText,{“Age”}),
    #”Replaced Value4″ = Table.ReplaceValue(#”Replaced Value3″,”+”,””,Replacer.ReplaceText,{“Age”}),
    #”Filtered Rows1″ = Table.SelectRows(#”Replaced Value4″, each ([Age] “totalt ålder”)),
    #”Inserted Custom” = Table.AddColumn(#”Filtered Rows1″, “EndOfYear”, each Date.From(“2013/12/31″)),
    #”Replaced Value5″ = Table.ReplaceValue(#”Inserted Custom”,” “,””,Replacer.ReplaceText,{“Population”}),
    #”Changed Type1″ = Table.TransformColumnTypes(#”Replaced Value5″,{{“Population”, Int64.Type}, {“Age”, Int64.Type}}),
    #”Removed Columns1″ = Table.RemoveColumns(#”Changed Type1″,{“Length”}),
    #”Filtered Rows2″ = Table.SelectRows(#”Removed Columns1″, each ([Population] 0)),
    #”Changed Type2″ = Table.TransformColumnTypes(#”Filtered Rows2″,{{“EndOfYear”, type date}})
    in
    #”Changed Type2″

Trackbacks

  1. Power View Maps differs from Excel and Excel services and how to solve it | Business Intelligence Info

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: