Connect to api rest power bi with power query, get the MS token

[k-vinh95]

Hello,

 I'm doing 2 tests. :

 - 1. retrieving the MS token from python code : The result is the good one

 - 2. The token from power query (I get a token but no the same as the python one, it means wrong) 

I don't understand why the token that I get from power query is different than the token from Python. 

I have made the call(on the 2 plateformes) at the same time to make sure to have to same token but it is not...

Can you tell me if the code below is wrong somewhere ?

Maybe the content-type is convert it wrong ? the text.ToBinary function ? 

Thanks a lot for your help

let
    RefreshAPIToken = () as text =>
    let
         token_url = "https://login.microsoftonline.com/[TENANT_ID]/oauth2/v2.0/token",

         client_id = "XXX",
          client_secret = "YYY",

          scope = "https://analysis.windows.net/powerbi/api/.default",
          grant_type = "client_credentials",
          body = "Client_Id=" & client_id & "&Client_Secret=" &client_secret &"&grant_type="&grant_type& "&scope="& scope ,

          payload =
         [
               Content = Text.ToBinary(body),
               Headers = [#"Content-Type"="application/x-www-form-urlencoded"]
         ],
         WebSource = Json.Document(Web.Contents( token_url, payload)),
         apikey = WebSource[access_token]
     in apikey
in RefreshAPIToken

[ams1]

Hi @k-vinh95 

I think the problem is that the body of the POST has to be urlencoded, and you've forgot to urlencode the scope.

Try below code from a question I've just answered: https://community.powerbi.com/t5/Power-Query/PowerBI-REST-API-amp-Access-Token-with-Power-Query/m-p/...

Please don't forget to mark this as answer if it helped.

() =>
    let
        client_id = "XXX",
        client_secret = "YYY",
        url = "https://login.microsoftonline.com/[TENANT_ID]/oauth2/v2.0/token",
        body = [
            grant_type = "client_credentials",
            scope = "https://analysis.windows.net/powerbi/api/.default",
            client_id = client_id,
            client_secret = client_secret
        ],
        GetJson = Json.Document(
            Web.Contents(
                url,
                [
                    Headers = [
                        #"Content-Type" = "application/x-www-form-urlencoded"
                    ],
                    Content = Text.ToBinary(Uri.BuildQueryString(body))
                ]
            )
        ),
        access_token = GetJson[access_token]
    in
        access_token

[k-vinh95 · Accepted solution]

Hi @ams1 ,

Thank you for your reply !

I finally understand and get a solution. 

There is a bizarre behaviour of the Power BI API REST.

You get a different token when you call the Power BI API REST from python and when you call from Power BI Desktop. ( with the same authentication for the both type of call )

But you have the same result at then end.

I was so convinced that you must have the exact same token that I lost a lot of time.

I hope this can help for the community.

[ams1]

Hi @k-vinh95 ,

Now I think I fully read/understood your initial question: both python and PowerQuery were working for you, BUT you were wondering why they returned different tokens - my first understanding was that PowerQuery was not working - sorry for that.

Well, I think that's how many API tokens usually work -> whenever you request a token, you get a new one.

Usually you can have more than 1 token valid for the same user/pass used.

The main thing to know is that usually ALL non-expired (long story...) tokens will "unlock the door".

I keep saying "usually" as the API developer is free to implement whatever token mechanism he wants.

[k-vinh95]

Thanks a lot @ams1 .

My bad, you were right and had a good understing of my first post. 

Actually, there were a another issue that my token in Power Query didn't even refresh. I noticed that when I took the token generated by PQ to use it into Python. It said " The token is expired" Then I understood that : 

First, the token is not the refreshed one and Two, the token for PQ and Python are different.

I modified my M code and I made this conlusion !

Thank you very much for your time and help. 

Have a good one