Adapter gets old values in Power BI, but correct when running from Visual Studio - what's happening?

[Syndicate_Admin]

Hi! 

I am developing an adapter with Power Query that is supposed to call our API for data after getting an access token through a token endpoint. It works fine now when running it from Visual Studio: It gets a token first, then successfully calls the API with that token to get data. 

However, when I build the adapter and run it in Power BI, I get a 401 response from our API. It turns out Power Query uses an old access token. Using Fiddler to inspect the network traffic, it seems there hasn't been a call to the token endpoint at all. 

So my questions are: 

- Why doesn't Power Query make a call to the token endpoint from Power BI while it works from Visual Studio? 

- Where does that old token come from? (It's weeks old according to the API error message) 

Is there some sort of caching going on here? 

(I am also open to suggestions on better ways to handling authentication here. I'm using a client ID / client secret authentication, and I don't see that as a built in option under the Handling Authentication guide) 

My somewhat messy test code for reference: 

Spoiler

    let
        // Fetching token 
        authKey = "Basic " & Binary.ToText(Text.ToBinary(client_id & ":" & client_secret),0),
        TokenUrl = OAuthBaseUrl & "/token",
        TokenResponse = Web.Contents(TokenUrl,
        [
            Headers = [#"Authorization"=authKey,
                       #"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
            Content = Text.ToBinary("grant_type=client_credentials")
        ]
        ),
        
        // Making a call to the API with fetched token
        AccessToken = Json.Document(TokenResponse)[access_token],
        AccessTokenHeader = "bearer " & AccessToken,
        RequestBody = "{""query"": ""query {applications(filter: {}) {referenceNumber} }""}",

        Response = Web.Contents(serviceUrl,
        [
            Headers = [#"Authorization"=authKey,
                       #"Accept"="application/json",
                       #"Content-Type"="application/json"],
            Content = Text.ToBinary(RequestBody)
        ]
        ),
        
        ResponseAsJson = Json.Document(Response),
        ContentsList = ResponseAsJson[data],

        #"Converted to Table" = Table.FromList(ContentsList[applications], Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"referenceNumber"}, {"referenceNumber"}),
    
        a = #"Expanded Column1"
    in
        a;

[Syndicate_Admin]

Hi @ivargj ,

Based on my experience, usually The server will turn on the security policy that prohibits crawlers. In this case, we need to add an identification flag to the headers of the request.

like this:

#"User-Agent"="Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/106.0.0.0 Safari/537.36 Edg/106.0.1370.47"]

Best Regards,

Stephen Tao

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

[Syndicate_Admin]

That worked! Thank you so much. 🙂 

The previous calls seemed to use this as user-agent:

 User-Agent: Microsoft.Data.Mashup (https://go.microsoft.com/fwlink/?LinkID=304225)

So I don't know why that didn't work. 

Isn't it also really strange how the symptom is that one of the calls doesn't go out, and the other goes out with a completely random old token? 

Anyways, it works now, so thanks for your help, @v-stephen-msft ! 

[Syndicate_Admin]

So apparently this did only fix the issue temporarily. I just got the same behavior once again, when trying to import some new data from the data source. 

What's funny is, if I change the User-Agent to something new, for instance by changing a version number, it works again for a while, then goes back to the same issue after a while. 

Pushing refresh on the data after including it in a report works. It usually happens when going into the Get data dialog, and sometimes from the Transform Data window. 

Is there something else I could try to get it more stable? Could this be a bug in Power BI / Power Query? Nonetheless it's some pretty strange behavior.