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
asomers21
Frequent Visitor

Web.Contents - we couldn't authenticate with the credentials provided (using anonymous credentials)

I am trying to connect to an API with bearer authentication. My first call to the API is to obtain the authentication token - this step seems to be successful. I then call the API again using the token to obtain the results I'm looking for - this is where I am stuck. I initially set the data source settings to Basic API using my login credentials, but I received the following error: "DataSource.Error: Web.Contents with the Content option is only supported when connecting anonymously." If I change the data source settings to use an anonymous connection, I get a different error: "We couldn't authenticate with the credentials provided. Please try again." I'm not sure what else to do! Any help would be greatly appreciated! Thanks in advance.

 

My code is below (I changed the url, username and password for privacy purposes):

 

 

let
    Source = Json.Document(Web.Contents("url" & "username=email" & "&password=password")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Transposed Table" = Table.Transpose(#"Converted to Table"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"access_token", type text}, {"refresh_token", type text}, {"token_type", type text}, {"expires_in", Int64.Type}}),
    #"Content" = Table.AddColumn(#"Changed Type", "Content", each "{
""search"": ""IRE"",
""startRecord"":1,
""recordCount"":100,
""includeAbstracts"":true,
""defaultFields"":""title,parentTitle,abstract"",
""includeCollections"": {
""filter"":""Id"",
""collectionIds"": [
23814
]
},
""SortOrder"":""PubYear"",
""SortDirection"":""Descending""
}
"),
    #"Changed Type1" = Table.TransformColumnTypes(Content,{{"Content", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "API", each Web.Contents("url", [Headers = [Authorization = [access_token]], Content = Text.ToBinary([Content])]))
in
    #"Added Custom"

 

 

 

12 REPLIES 12
Feliba
Regular Visitor

Hello from Uruguay,

Finally you could acces? Or you have failed in the diferents attempts? I am having the same problem and I dont know what to do

 
 

Unfortunately, I never had any luck solving this issue and had to pull in the data using an entirely different workflow.

v-shex-msft
Community Support
Community Support

HI @asomers21.

I'd like to suggest you add the relative path to split your url string to root url and relative path to enable anonymous credentials on root level.

Refresh not working in Power BI Service 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft - Thank you very much for the reply!

 

Unfortunately, this hasn't resolved my issue. I used RelativePath in both instances of Web.Contents (see edited code below), but I still receive the same error message: "We couldn't authenticate with the credentials provided. Please try again." When I try to edit the credentials and set them to anonymous, the url options in the dropdown for selecting which level to apply these settings to still contain the relative path and doesn't give me the option to select just the root path. If you see any issues with my code or have any other ideas, I'd greatly appreciate it. Thanks!

 

 

Capture.JPG

 

let
    Source = Json.Document(Web.Contents("https://www.url.com", [RelativePath = "restapi/v1/token?" & "username=email" & "&password=password"])),
    #"Converted to Table" = Record.ToTable(Source),
    #"Transposed Table" = Table.Transpose(#"Converted to Table"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"access_token", type text}, {"refresh_token", type text}, {"token_type", type text}, {"expires_in", Int64.Type}}),
    #"Content" = Table.AddColumn(#"Changed Type", "Content", each "{
""search"": ""nanoknife"",
""startRecord"":1,
""recordCount"":100,
""includeAbstracts"":true,
""defaultFields"":""title,parentTitle,abstract"",
""includeCollections"": {
""filter"":""Id"",
""collectionIds"": [
23814
]
},
""SortOrder"":""PubYear"",
""SortDirection"":""Descending""
}
"),
    #"Changed Type1" = Table.TransformColumnTypes(Content,{{"Content", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "API", each Web.Contents("https://www.url.com", [Headers = [Authorization = [access_token]], Content = Text.ToBinary([Content]), RelativePath = "restapi/v1/Search/query"]))
in
    #"Added Custom"

 

 

I ran into the same problem. After some testing it wasn't my code but a setting in your Power Bi "Source authentication settings". You need to add your head URL to the "general authentication" and not only to the "Authentication in current file".

1) Go to "File" and select "options en settings". Than choose "settings for datasource".

Step 1.png

Next select "edit authentication".

2) Select "edit" again.

Monkeybiznizz_0-1644345945625.png

3) Make sure you only have your head URL in the authentication. And Save.

Monkeybiznizz_2-1644346056676.png

 

4) Now your Url will be added to the "General authentication" tab. This solved it for me.

Monkeybiznizz_3-1644346253688.png


Hope this helps.

 

 

 

Update: I tried clearing the permissions for the data source and resetting them to anonymous. I am now given the option to select just the root url to apply the settings to, but I still continue to get the same error that they are unable to authenticate the credentials.

HI @asomers21,

You can try to use the following query if it works on your scenario: (I moved the 'access token' and 'verify part' to 'Query' optional parameter)

let
	root="www.url.com",
	relativepath="/restapi/v1/",
    Source = Json.Document(Web.Contents(root, [RelativePath = relativepath,Query=["token?username="&email& "&password="&password]]),
    #"Converted to Table" = Record.ToTable(Source),
    #"Transposed Table" = Table.Transpose(#"Converted to Table"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"access_token", type text}, {"refresh_token", type text}, {"token_type", type text}, {"expires_in", Int64.Type}}),
    #"Content" = Table.AddColumn(#"Changed Type", "Content", each "{
""search"": ""IRE"",
""startRecord"":1,
""recordCount"":100,
""includeAbstracts"":true,
""defaultFields"":""title,parentTitle,abstract"",
""includeCollections"": {
""filter"":""Id"",
""collectionIds"": [
23814
]
},
""SortOrder"":""PubYear"",
""SortDirection"":""Descending""
}
"),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "API", each Web.Contents(root,[RelativePath = relativepath&"Search/query", Content = Text.ToBinary([Content]), Query=[access_token=[access_token]]]))
in
    #"Added Custom"

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft Thanks for the suggestion!

 

When I use your code, I get the following error on the first Web.Contents reference:

 

DataSource.Error: Web.Contents failed to get contents from "www.url.com/restapi/v1/" (404): Not Found

Details:

DataSourceKind=Web

DataSourcePath=www.url.com/restapi/v1

Url=www.url.com/restapi/v1/

 

I'm guessing this is because www.url.com/restapi/v1 is not a valid destination. www.url.com is a valid destination, but www.url.com/restapi/v1 is not without the token and credentials part of the path.

 

HI @asomers21,

Since I haven't test with similar samples, maybe you can try to use the below formula, I modify the relative path option.

let
	root="www.url.com",
    Source = Json.Document(Web.Contents(root, [RelativePath = "/restapi/v1/token?username="&email& "&password="&password])),
    #"Converted to Table" = Record.ToTable(Source),
    #"Transposed Table" = Table.Transpose(#"Converted to Table"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"access_token", type text}, {"refresh_token", type text}, {"token_type", type text}, {"expires_in", Int64.Type}}),
    #"Content" = Table.AddColumn(#"Changed Type", "Content", each "{
""search"": ""IRE"",
""startRecord"":1,
""recordCount"":100,
""includeAbstracts"":true,
""defaultFields"":""title,parentTitle,abstract"",
""includeCollections"": {
""filter"":""Id"",
""collectionIds"": [
23814
]
},
""SortOrder"":""PubYear"",
""SortDirection"":""Descending""
}
"),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "API", each Web.Contents(root,[RelativePath = "/restapi/v1/Search/query", Content = Text.ToBinary([Content]), Query=[access_token=[access_token]]]))
in
    #"Added Custom"

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft I greatly appreciate all of your help on this. Unfortunately, making this change brings me back to the previous error ("We couldn't authenticate with the credentials provided"). The error remains regardless of which level of the url I select when applying the anonymous access to (the root url or the full url).

Hi @asomers21 

In fact, I haven't test with similar structure datasource so I can't confirm if these formulas fully works. Maybe Imkef can share some useful suggestions, she is familiar with M query. @ImkeF Any idea for this scenario?

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @asomers21 

I haven't used this kind of authentication method before, so cannot share any tips from experience.

What I would do in such a case is to use Postman to create the steps that run successful as a benchmark and then use Fiddler to monitor what's happening when PBI query is executed. With any luck, you can identify the step that causes the problem. Compare the headers created: Maybe you have to add some fields into PQ.

But unfortunately it could well be that you have to build a custom connector instead.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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.