Forum Discussion

JoseJunior22's avatar
JoseJunior22
New Member
4 years ago
Solved

Power BI message- M Language - Failure to Request a json service API -

Hi, people. I really need a helping hand, since i've been trying to solve this problem for one week, but it seems to be impossible to get it alone. The question is: this "M" code (below) makes a https connection to an endpoint. It runs fine until the step which receives the token finhishes: the authentication process is running well so far. Unfortunatelly, aftter this previous step, when i call a JSON service by the second time, the Power BI show the message: "Specify how to connect". So, Power BI shows me a window to choose the authentication method to allow the conection. Then, I select the anonymous option. After that, Power BI shows me another  message box; "it's not possible to authenticate with the credencials".  The problem seems to be at the urlRequest object. Any help would be very apreciated! Thanks in advance, José. 

 

Here is the M code:

 

let
body = "{
""grant_type"" : ""client_credentials""
}",

minhaURL = "https://api.anbima.com.br/oauth/access-token",
getToken = Json.Document(Web.Contents(minhaURL,
[
Headers=[#"Content-Type"="application/json",
#"Authorization" = "Basic "&Binary.ToText(Text.ToBinary("Uw6QvcZwzRtp:qaRCeKStnXhE"), 0)
],
Content=Text.ToBinary(body)])
),

access_token = getToken[access_token],
authToken = "bearer " & access_token,

//urlRequest= path & "/feed/precos-indices/v1/titulos-publicos/curvas-juros",
urlRequest = "https://api-sandbox.anbima.com.br/feed/precos-indices/v1/titulos-publicos/mercado-secundario-TPF",
//keyPhrases = KeyPhrases(),
results = Json.Document(Web.Contents(
urlRequest,
[
Headers=[#"Content-Type"="application/json",
client_id = "Uw6QvcZwzRtp",
access_token = authToken
]

// Headers=[Accept="application/json",#"Authorization"=authToken]
// Headers = [#"Authorization"=authToken]
]
)
)
in results

The window below show the problem. The text is written in portuguese language.

 

 

 

    

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    HI JoseJunior22,

    I think this may be related to your API authorization, the 'anonymous' mode is set on the root URL instead of the detailed API URL. Perhaps you can add the relative path option to handle this scenario:

    let
    body = "{'grant_type' : 'client_credentials'}",
    rootURL = "https://api-sandbox.anbima.com.br",
    _tokenPath = "/oauth/access-token",
    getToken =
        Json.Document(
            Web.Contents(
                rootURL,
                [
                    Headers = [
                        #"Content-Type" = "application/json",
                        #"Authorization" =
                            "Basic "
                            & Binary.ToText(
                                Text.ToBinary("Uw6QvcZwzRtp:qaRCeKStnXhE"),
                                0
                            )
                    ],
                    RelativePath = _tokenPath,
                    Content = Text.ToBinary(body)
                ]
            )
        ),
    access_token = getToken[access_token],
    authToken = "bearer " & access_token,
    //urlRequest= path & "/feed/precos-indices/v1/titulos-publicos/curvas-juros",
    _relativePath = "/feed/precos-indices/v1/titulos-publicos/mercado-secundario-TPF",
    //keyPhrases = KeyPhrases(),
    results =
        Json.Document(
            Web.Contents(
                rootURL,
                [
                    Headers = [
                        #"Content-Type" = "application/json",
                        client_id = "Uw6QvcZwzRtp",
                        access_token = authToken
                    ],
                    RelativePath = _relativePath
                // Headers=[Accept="application/json",#"Authorization"=authToken]
                // Headers = [#"Authorization"=authToken]
                ]
            )
        )
in
    results

    Chris Webb's BI Blog: Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code Chris Webb's BI Blog (crossjoin.co.uk)

    Regards,

    Xiaoxin Sheng

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    HI JoseJunior22,

    I think this may be related to your API authorization, the 'anonymous' mode is set on the root URL instead of the detailed API URL. Perhaps you can add the relative path option to handle this scenario:

    let
    body = "{'grant_type' : 'client_credentials'}",
    rootURL = "https://api-sandbox.anbima.com.br",
    _tokenPath = "/oauth/access-token",
    getToken =
        Json.Document(
            Web.Contents(
                rootURL,
                [
                    Headers = [
                        #"Content-Type" = "application/json",
                        #"Authorization" =
                            "Basic "
                            & Binary.ToText(
                                Text.ToBinary("Uw6QvcZwzRtp:qaRCeKStnXhE"),
                                0
                            )
                    ],
                    RelativePath = _tokenPath,
                    Content = Text.ToBinary(body)
                ]
            )
        ),
    access_token = getToken[access_token],
    authToken = "bearer " & access_token,
    //urlRequest= path & "/feed/precos-indices/v1/titulos-publicos/curvas-juros",
    _relativePath = "/feed/precos-indices/v1/titulos-publicos/mercado-secundario-TPF",
    //keyPhrases = KeyPhrases(),
    results =
        Json.Document(
            Web.Contents(
                rootURL,
                [
                    Headers = [
                        #"Content-Type" = "application/json",
                        client_id = "Uw6QvcZwzRtp",
                        access_token = authToken
                    ],
                    RelativePath = _relativePath
                // Headers=[Accept="application/json",#"Authorization"=authToken]
                // Headers = [#"Authorization"=authToken]
                ]
            )
        )
in
    results

    Chris Webb's BI Blog: Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code Chris Webb's BI Blog (crossjoin.co.uk)

    Regards,

    Xiaoxin Sheng