Power Query - How to format options for Web.Content()? RRS feed

  • Question

  • I am trying to access a web api which can return data in either JSON or XML. When connecting with Power Query I get a "400, Bad request" error though and I suspect it could be due to Power Query not explicitly providing accepted Content-Types in the request. Anyone know how to add options to the Web.Content() request?



    Saturday, November 2, 2013 8:34 AM


  • It is not currently possible to add arbitrary headers to the request made by Web.Content() or to request a specific Content-Type.

    • Marked as answer by Daniel Wikar Tuesday, November 5, 2013 10:09 PM
    Monday, November 4, 2013 4:46 PM

All replies

  • Hi Wikar,

    Do you know what is the default response content-type for your API? Power Query supports both JSON and XML so as far as the response is in one of those formats, PQ will automatically process the output as needed without having to specify any extra option.


    Monday, November 4, 2013 4:25 PM
  • It is not currently possible to add arbitrary headers to the request made by Web.Content() or to request a specific Content-Type.

    • Marked as answer by Daniel Wikar Tuesday, November 5, 2013 10:09 PM
    Monday, November 4, 2013 4:46 PM
  • I believe the default reponse content-type is JSON. It doesn't serve Power Query with anything but an error response though and with the help of Network Monitor I caught a bit more details (in Swedish).


    Http: Request, GET /platsannons/matchning, Query:lanid=1
        Command: GET
      + URI: /platsannons/matchning?lanid=1
        ProtocolVersion: HTTP/1.1
        Accept:  */*
        UserAgent:  Mozilla/5.0 (compatible; Microsoft.Data.Mashup; http://go.microsoft.com/fwlink/?LinkID=304225)
        Host:  api.arbetsformedlingen.se
        Accept-Encoding:  gzip, deflate
        Connection:  Keep-Alive
        HeaderEnd: CRLF


    HTTPPayloadLine: {"Error":{"statuskod":400,"titel":"Bad Request","beskrivning":"Felaktig headerparameter: [Accept-Language]"}}
    And the API can be found at:

    Would be great to be able to define custom headers to the request through options for cases like these. Thanks for the reply!

    Best regards,

    • Proposed as answer by Roland G29 Thursday, December 4, 2014 7:29 PM
    Tuesday, November 5, 2013 10:19 PM
  • We've now added this ability to Web.Contents. You can say something like

    Web.Contents("http://api.arbetsformedlingen.se/platsannons/matchning?lanid=1", [Headers=[#"Accept-Language"="se-SE"]])

    and it should work for this site.

    The December preview can be downloaded from http://www.microsoft.com/en-us/download/details.aspx?id=39933&WT.mc_id=blog_PBI_Update_PowerQuery

    Saturday, December 14, 2013 2:47 PM
  • Just works like a charm! I was able to combine headers without a hassle as well, great work and perceptiveness!
    Saturday, December 14, 2013 9:39 PM
  • This is a huge win!  Thanks
    Thursday, January 9, 2014 6:06 PM
  • How do you specify multiple header key values?
    Tuesday, February 4, 2014 9:03 AM
  • You can put as many fields into the headers record as you like. e.g.

    Web.Contents("http://some.url", [Headers=[#"Accept-Language"="se-SE", #"Content-Type"="application/json"]])

    Tuesday, February 4, 2014 4:48 PM
  • Does anyone know how to handle seeing the following message when using the Advanced Editor?

    Update: It looks like I need to pass an auth token or cookie somehow...

    • Edited by Roland G29 Thursday, December 4, 2014 7:55 PM
    Thursday, December 4, 2014 7:38 PM
  • Yes, by using "anonymous" inside Power Query, you would have to construct the authentication information manually but can then use any header values. I'm not sure why "Accept" isn't on our list of approved headers and will look at adding that.
    Thursday, December 4, 2014 11:56 PM
  • Can you also look into why Content-Type requires anonymous connections?

    This blocks me from using power query to retrieve testrail test results which requires Basic authentication + Content-Type: application/json (http://docs.gurock.com/testrail-api/accessing).

    I tried using anonymous data connection, then including credentials inside the Headers, but it wouldn't work.

       actualUrl = "https://testrail.server.com/index.php?/api/v2/get_results_for_run/12345",
       options = [Headers =[#"Content-type"="application/json",#"Authorization"="username:password"]],
       result = Web.Contents(actualUrl, options)

    Friday, March 6, 2015 11:29 PM
  • If you specify anonymous credentials in the credential dialog and pass the Authorization explicitly, you should be able to use any header you like. What kind of error are you getting?
    Friday, April 3, 2015 10:18 PM
  • Hi Mikeli77

    Were you able to resolve this issue? I'm have a similar problem - trying to pass the credentials for basic authentication in the URL.

    Wednesday, August 12, 2015 11:40 AM
  • Realizing this is an old question, but wanted to note this. 

    If you are using Basic authentication, the correct "Authorization" header is the base64-encoded version of "username:password", so the Header should be

    #"Authorization" = "(base64-encoded username:password)".

    If you're using a token, the authorization header is usually "Bearer" followed by the token (i.e.,

    #"Authorization" = "Bearer (token)"

    Friday, March 4, 2016 4:52 PM
  • how could I use POST method using username and password?
    I am getting error 405 METHOD NOT ALLOWED on my code below. It seems username and password are not pushed inside API.
    I am new to M language and not used to the correct syntax I think...

         apiUrl = "http://xxxxx",
         options = [Headers =[#"Content-Type"="application/json",
                            #"Authorization" = "(base64-encoded username:password)"]],
         Value = Web.Contents(apiUrl,options)


    Thursday, October 26, 2017 5:14 PM
  • Trying to use Cognitive Services Translate Text service in AAS/Power BI … keep getting Error: 400 bad request

    Code snippet:




    body =  "{""Text"":""Have a good day""}",


        Source = Json.Document(Web.Contents("https://api.cognitive.microsofttranslator.com/translate?api-version=3.0&to=fr-FR",




    Headers=[#"Ocp-Apim-Subscription-Key"="xxxxxxxxxxx", #"Content-Type"="application/json"],


    Content= Text.ToBinary(body)


    ] ))






    what am I missing?

    Friday, October 19, 2018 6:02 PM