0

I am trying to extract data from an api using power query for further analysis but unfortunately its throwing an error saying we couldnt authenticate with credentials provided, please try again. The api uses basic authentication and there are seperate end points for log in (authentication) and other structured data. Please see below below code I have used in my power query ( entire code in same query)

`Step 1

Mainurl = "url"
path = " api/v1/auth/login"
login = Json.Document( Web.Content ( Mainurl,
RelativePath = path,
[
Header = [#"Content Type" = "application/json", #"Authorisation" =  Basic < base 64 encoded username : password > ]
]
))

Token =Text.From(Record.Field(login,"antiCsrfToken") -- capture token in a variable

Step 2
datarequestAPI = "api/vendor/transaction"
Datarequest =Json.Document( Web.Content ( Mainurl,
RelativePath =  datarequestAPI ,
[
Headers = [#"Content Type" = "application/json", #"Authorisation" = "Bearer "& Token ]
]
))`

While debugging the code everything works well for step 1 block ie the login works well and it returns an antiCsrfToken which I then pass into a variable to use in step 2. But unfortunately its erroring out in step 2 saying we couldnt authenticate with credentials provided

  1. tried api/vendor/transaction method without providing token in the header as I didnt see an authorisation part in parameters as per API documentation. the cURL contained only mainurl- path - query parameters. The header consisted of only Accept : application/json. Naturally I thought this method doesnt require a token as parameter. But it didnt work

  2. Provided the token as an APIKey in the header in step 2. This also failed

  3. Provided Bearer token authorisation in header ( above code ) - with and without space after 'Bearer'

  4. With and without text convertion step for token variable.

  5. tried to construct the authentication using UI

All the above steps were followed by clicking data source setting and making sure the connection type = anonymous and privacy = none ( tried privacy = none, public, organisation )

All my attempts failed with same error "we couldnt authenticate with credentials provided, please try again". The fact that the login method is working fine and but the next step failing is baffling me. Must be something to do with how I am passing the token in the next step "api/vendor/transaction" method or if at all token is required to extract the data. I am not sure how else I can let the server know that I have done the login and please trust me and give me my data!!.

User8985
  • 1
  • 3
  • are you able to make it work with another tool like Fiddler? – mxix Aug 03 '23 at 21:51
  • Hi mxix, never used that tool before. would open a entirely new frontier in my learning path if I venture down that route. but sadly I have restriction in using any new tools. – User8985 Aug 03 '23 at 23:31
  • just wanted to discard the possibility that the API actually works. That message does not look like a Power BI error. "we couldnt authenticate with credentials provided, please try again" it looks like there might be some issues with your credentials – mxix Aug 04 '23 at 10:13
  • makes sense. Thanks. Appreciate your thoughts and comments on this.. I have tested the endpoints through swagger and its all working fine using my credentials. But in swagger once we execute the login method , then the rest of the endpoints works fine. Like how you do in powerbi you dont have to send the anticsrf token back. Looks like all of that happens by default in swagger.. or what i am not sure about is the sending the token back step is required at all... i'm still learning and doesnt have a clear picture how all of these should link and work together. – User8985 Aug 04 '23 at 12:10
  • which kind of makes it diffcult to figure out how I go about from here – User8985 Aug 04 '23 at 12:11

0 Answers0