Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
I'm trying the basic auth API method and getting the error below.
DataSource.Error: Web.Contents with the Content option is only supported when connecting anonymously.
Any ideas?