Paginated report - Web API authentication with POST request

[kalluu91]

Hi experts,

Context

- I use Power BI Paginated Report

- Data Source is coming from a Web API

Issue

For the moment, I need to execute Web API with POST action with Organizational Account authentication.

I got the error "DataSource.Error: Web.Contents with the Content option is only supported when connecting anonymously." when trying to POST the message as below:

let
  Body = "{""SearchFilter"":""{""missionIds"":[""5e407379-446c-ef11-bfe2-000d3a24b450""]}""}",
  Source = Json.Document(Web.Contents("<my_web_api_url>", [Content = Text.ToBinary(Body)])),
  #"Converted to table" = Table.FromRecords({Source}),
  #"Expanded value" = Table.ExpandListColumn(#"Converted to table", "value"),
  #"Expanded value1" = Table.ExpandRecordColumn(#"Expanded value", "value", {"name", "kind", "url"}, {"value.name", "value.kind", "value.url"}),
  #"Changed column type" = Table.TransformColumnTypes(#"Expanded value1", {{"@odata.context", type text}, {"value.name", type text}, {"value.kind", type text}, {"value.url", type text}})
in
  #"Changed column type"

When I try using the Anonymous, I get another error "The credentials provided for the Web source are invalid."

Ref: https://learn.microsoft.com/en-us/power-bi/paginated-reports/report-builder/connect-snowflake-databr... 

Many thanks,

Kim Anh

[v-tangjie-msft · Accepted solution]

Hi @kalluu91 ,

When you make POST requests in Power Query you must use Anonymous authentication when asked,  then in the actual query you supply the username and password and the authentication is performed via the Headers of the POST request.

let
    url = "api.xxxx.com",
    body = "{}",
    token = "xxxxxxxx",
    header = [
        #"Content-Type" = "application/json",
        #"Authoritation" = "Basic " & token
    ],
    response = Web.Contents(url, [
        RelativePath = "/xxxx/login",
        Headers = header,
        Content = Text.ToBinary(body)
    ]),
    AccessToken = Json.Document(response)[access_token],
    JsonResponse = Web.Contents(
        url,
        [
            RelativePath = "/xxx/xxx",
            Headers = [
                Authoraztion = "Basic " & AccessToken,
                #"Content-Type" = "application/json"
            ]
        ]
    ),
    Result = Json.Document(JsonResponse)
in
    Result

I found some posts with similar issues that you can refer to:

Solved: Web.Contents with the Content option is only suppo... - Microsoft Fabric Community

Solved: DataSource.Error - Microsoft Fabric Community

Solved: Error with Web.Contents and POSTing to a URL that ... - Microsoft Fabric Community

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.