Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
netwire
Frequent Visitor

Connect with secure JSON source

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

2 REPLIES 2
dstramilov
Helper II
Helper II

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors