cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Token authentication error with oData source pagination

Hi all,

I have written a function (fnGetBearerToken) which returns a bearer token from oData source using some parameters:

 

 

() as text =>
let
authKey = "{""ClientId"":""" & ClientId & """,""ClientSecret"":""" & ClientSecret & """}",
url = "https://trust.citrixworkspacesapi.net/" & TenantId & "/tokens/clients",
// Uses the Citrix Cloud POST oauth2/token method to obtain a bearer token
GetJson = Web.Contents(url,
    [
        Headers = [#"Accept"="application/json",
                #"Content-Type"="application/json"],
        Content = Text.ToBinary(authKey) 
    ]
),
FormatAsJson = Json.Document(GetJson),
// Gets token from the Json response
AccessToken = FormatAsJson[token]

in
    AccessToken

 

 

  This function then works successfully with this query:

 

 

let
 AccessTokenHeader = "CwsAuth Bearer=" & fnGetBearerToken(),
 // Uses the Citrix Cloud GET method using the bearer token from the previous POST oauth2/token method
 GetJsonQuery = Web.Contents("https://" & TenantId & ".xendesktop.net/Citrix/monitor/OData/v4/data/Users",
     [
         Headers = [#"Authorization"=AccessTokenHeader,
                    #"Customer"=TenantId]
     ]
 ),
 FormatAsJsonQuery = Json.Document(GetJsonQuery),
    value = FormatAsJsonQuery[value]
in
    value

 

 

 However, the oData query only return 100 records, so pagination is required, which you can see is supported:

2020-03-31_203304.png

 

 

 

This is where the problem occurs. When I iteratively query the nextLink URL, I get the authentication error "We couldn't authenticate with the credential provided"

2020-03-31_210102.png

 

Here's the code used:

 

 

let
 AccessTokenHeader = "CwsAuth Bearer=" & fnGetBearerToken(),
 
fnGetCitrixCloudOData = (Path as text, AccessTokenHeader as text) =>
let

    Source = Json.Document(Web.Contents(Path, 
        [
            Headers=[Authorization=AccessTokenHeader, Customer=TenantId]
        ])),

    // Assign the resultant List object to NextList variable
    NextList = @Source[value],

    // recursively call fnGetCitrixCloudOData function using the value of @odata.nextLink as the path
    // Since @odata.nextLink is not guaranteed to always exist in the case where the result is less 
    // than 100 we need to catch this and deal with it using a try otherwise basically means if you 
    // error out because no @odata.nextLink exists then just return the resulting list of values
    result = try @NextList & @fnGetCitrixCloudOData(Source[#"@odata.nextLink"], AccessTokenHeader) otherwise @NextList

in
    // Return the result variable as the function return
    result,

oData = fnGetCitrixCloudOData("https://" & TenantId & ".xendesktop.net/Citrix/monitor/OData/v4/data/Users", AccessTokenHeader)

in
    oData

 

 

 I have also tried the method described in the blog, with the same outcome: https://datachant.com/2016/06/27/cursor-based-pagination-power-query/

 

Being fairly new to M Query, I feel a bit out of my depth about now, so any help is appreciated!

 

Thanks,

Jason

1 REPLY 1
Highlighted
Community Support
Community Support

Re: Token authentication error with oData source pagination

Hi @JasonP76 , 

I am not familiar with pagination api, you could try to refer to how-to-do-pagination-in-power-query  for details or @Imkef(who is professional in power query) or other super users for more suggestions.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021