Connect with secure JSON source

netwire · ‎05-22-2016

Hi,

I'm a newbie to PowerBI. I know that PowerBI can connect to pre-built web services like Google Analytics, but how do I connect to other online sources that aren't part of PowerBI yet but require authentication? For example, many JSON sources require HTTP Basic Authentication. I looked into oData Feed but I don't think it works with JSON data sources. I also read http://superuser.com/questions/977249/loading-a-json-file-into-power-query/977376#977376 which connects with publicly viewable JSON API's (and then convert them to PowerBI table), but I don't think that works with secure API's. Any ideas on how to address this?

Dean

dstramilov · ‎05-22-2016

netwire,

for your API example below use Get Data - Web - in Power Bi Desktop. http://api.nobelprize.org/v1/laureate.json

or use this

let
    Source = Json.Document(Web.Contents("http://api.nobelprize.org/v1/laureate.json")),
    laureates = Source[laureates],
    #"Converted to Table" = Table.FromList(laureates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "firstname", "surname", "born", "died", "bornCountry", "bornCountryCode", "bornCity", "diedCountry", "diedCountryCode", "diedCity", "gender", "prizes"}, {"id", "firstname", "surname", "born", "died", "bornCountry", "bornCountryCode", "bornCity", "diedCountry", "diedCountryCode", "diedCity", "gender", "prizes"})
in
    #"Expanded Column1"

for basic auth API, use something like this.

let
 authKey = "{""Username"":""USERNAME"",""Password"":""PASSWORDGOESHERE"",""LocationID"":""LOCATIONIFNEEDED""}",
 url = "https://APILINKGOESHERE.com",
 // Uses the authentication/token method to obtain a token
 GetJson = Web.Contents(url,
     [
         Headers = [#"Content-Type"="application/json"],
         Content = Text.ToBinary(authKey),
         RelativePath="/Authentication/AuthorizeUser"
     ]
 )
in
    GetJson