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
medwards807
Helper I
Helper I

How to Authenticate to a REST API with basic Authentication in Power BI Blank Query

I have a need to access a REST API (JSON) to access data in CSV format.

 

https://apiURL/apps/api/batch-query/

 

With Header as follows:

 

Authorization = "Basic <encoded username : password>"

Content-Type = "application/json"

 

My code is as follows:

 

let
 source = #!"Json.Document(Web.Contents(""https://apiURL.com/apps/api/batch- query/"", [#Authentication=""Basic <encoded username : password""]))"
in
 source

 

I receive error saying the header authorization can not be used. Most examples I have seen are using token/key, this API does not have that capability. Using a REST Client the call works just fine.

 

Is there a simple way to make a connection to the API with basic Auth, I need to do a POST, GET, GET (each requests will use a value from the previous request.

 

Thanks

10 REPLIES 10
Anonymous
Not applicable

I have a need to access a REST API (JSON) to access data in CSV format.

 

GET https://fb.satmetrix.com/app/core/v1/feedback/1550421980492230

 

 

I am getting an error when I tried submit basic authentication string. It shows authentication cannot be provided,Please check your credential 

 

Is there a simple way to make a connection to the API with basic Auth, I need to do GET data

Did you find a solution for this?

Youssef
Employee
Employee

Power BI Desktop supports basic authentication out of the box.

 

You can remove the authentication part in your Web.Contents call and just call your URL. Something like:

Json.Document(Web.Contents("https://apiURL.com/apps/api/batch-query"))

 

Power BI will prompt you to authenticate and will provide multiple options. Just pick the one called "Basic" and enter the username and password. That should automatically insert the base64 encoded username/password in the authentication header.

 

Hope this helps.

Youssef Shoukry
Product Manager, Power BI

 

Youssef,


Thanks for the information, I gave it a try and it didn't work, when prompting for credentials and selecting basic and entering Username and Password it did not work. However I was able to get the following code to work.

 

Note: I just had to set the Data Source to Anonymous

 

let
   actualUrl = "https://apiurl.com/apps/api/batch-query/",
   options = [Headers =[#"Content-type"="application/json",
              #"Authorization"="Basic <encoded username : password>"]],
   result = Web.Contents(actualUrl, options),
   #"Imported JSON" = Json.Document(result,1252)
in
   #"Imported JSON"

 

I would be interested in your opinion on this vs your original suggestion? or maybe some insight on how this works.

 

In addition I need to make this a POST Request, in reading it sounds like I just need to add a [Content=Text.ToBinary(PostContents)])

 

Where PostContents = JSON Text, can you provide any insight on how I would add the Post Content into my request?

 

{
"format" : "csv",
"version" : "1.1",
"name" : "Example",
"encrypted" : "none",
"useQueryLabels" : "true",
"dateTimeUtc" : "true",
"queries"  : [ {
    "name" : "Subscriptions",
    "query" : "select * from Subscription",
    "type" : "export"
 },
 {
 "name" : "Accounting",
    "query" : "select Id,StartDate,EndDate,FiscalYear,Name,Status from AccountingPeriod",
    "type" : "export"
   }]
}

This is what works for me:

 

Source = Json.Document(Web.Contents(address,
[
Headers = [#"Content-Type"="application/json", #"Authorization"="Basic asdasdasdSDASDADasdasd"]
] )),

hugoberry
Responsive Resident
Responsive Resident

Here is a POST request I used for dropbox API call

let
    data = [    path= "/code",
                recursive=false,
                include_media_info=false,
                include_deleted=false,
                include_has_explicit_shared_members=false],
    header = [  #"Authorization"="Bearer ZZZZZZZZZZZ",
                #"Content-Type"= "application/json"],
    response = Web.Contents("https://api.dropboxapi.com/2/files/list_folder",[Content=Json.FromValue(data),Headers=header]),
    out = Json.Document(response,1252)
in
    out

As for why your particular request worked I believe is that because of how your service API is configured. 

#"Authorization"="Basic <encoded username : password>"

 

The approach that @Youssef was mentioning also should have worked, but I know that usualy this implies that some portions of your authentication is sent over to the service adrress in plain text, and your service might have restrictions on that. Another possibility is that your service's hashing algorithm for username:password might differe from that of Power BI.

 

By the way, I had problems making POST request via Web.Contents only when using Active Directroy. As long as you include some sort of authenticated token in the Header of your request, you should be fine.

Hi Guys, 

 

Sorry if I didn't understand correctly... Please correct me if I didn't. You are adding your credentials within the code ? There is no way to protect it ? 

 

Because here I see you are encoding it, but it's only to make sure the information is correctly transmitted, right ? 

 

Is there any way to use an API without having to show your password ? (like in the Basic Authentication, at least your password is not "in clear")

 

Thank you very much for your help

 

Best regards,

Abdel

The easiest way to know why the authentication didn't work is by using Fiddler to compare the requests made when you used the OOTB basic authentication vs. your workaround. The built-in basic auth should create this header for you and attach it to every request.

 

Making Post requests in Power BI Desktop to APIs is not supported at the moment (if you use the Content option as you mentioned, you'll get a "Method Not Allowed" error). We are working on some Power BI Desktop extensibility capabilities that would enable you to do things like that and more. There is no exact ETA at the moment, but we are shooting for the first half of 2017.

 

Hope this helps.

Youssef Shoukry
Product Manager, Power BI

Has there been any updates to this? I'm trying to connect a REST API (using POST) to PowerBI with basic authentication.

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