Jump to content
Welcome to our new Citrix community!
  • 0

Power bi retrive the 100 raw with odata


Question

Hello, Power Bi is new to me and I managed to connect to the odata on my Citrix Daas, it works. However, I can only retrieve the first 100 raw. In the Citrix documentation it says that there are only the first 100 raw and that you have to use @odata..nextlink (https://developer-docs.citrix.com/en-us/citrix-cloud/accessing-monitor-service-data-citrix-cloud-external/accessing-monitor-service-data-citrix-cloud-external) but I don't see how to use it. I could use some help.

Link to comment

4 answers to this question

Recommended Posts

  • 0

Dear Darryl,

 

I already use this link to test my Power Bi.

But that show me the 100 raw and have to find how to have to 100 next, and so on...

 

I saw in documentation this :

Pagination

The monitor API returns 100 records per call, so if your script is designed to query more than 100 records, you need to modify the script to handle pagination or to filter the results correspondingly, for example, using a live query.

 

But as i said I,m newbe in power Be.

Link to comment
  • 0

So I create a new query to get my token :

image.thumb.png.5eb805ea51db95232d868ad6fbb02dcb.png

 

GetAccessToken

let

    tokenUrl = "https://api-eu.cloud.com/cctrustoauth2/root/tokens/clients", // get citrix cloud API credential (bearer token)

    headers = [

    #"customerid" = "your citrix customer id", // CCID displayed in citrix cloud tenant

    #"Content-Type" = "application/x-www-form-urlencoded", //format for powerbi

    #"Accept" = "*/*"

    ],

    postData = [

    grant_type = "client_credentials",

    client_id = "Your Citrix API Key", // here your API key from cloud.com identity

    client_secret = "Your Citrix Secret" // here your API secret

    ],

    response = Json.Document(Web.Contents(tokenUrl, [Headers = headers, Content = Text.ToBinary(Uri.BuildQueryString(postData))])),

    // get the CC bearer token from the response and add the in header expected 'CwsAuth bearer' in front

    access_token = "CwsAuth bearer=" & response[access_token]

in

    access_token

 

 

after I create a new query for user.

 

image.thumb.png.44c57b795f8defa90a2c2d548c02d56b.pnglet

    BaseUrl         = "https://api-us.cloud.com/monitorodata/users",

    EntitiesPerPage = 100,

 

    GetJson = (Url) =>

        let Options = [

        Headers=[

            authorization=GetAccessToken,

            #"Citrix-CustomerId"="your customer id"]

      ],

            RawData = Web.Contents(Url, Options),

            Json    = Json.Document(RawData)

        in  Json,

 

    GetEntityCount = () =>

        let Url   = BaseUrl & "?$count=true&$top=0",

            Json  = GetJson(Url),

            Count = Json[#"@odata.count"]

        in  Count,

 

    GetPage = (Index) =>

        let Skip  = "?$skip=" & Text.From(Index * EntitiesPerPage),

            Top   = "$top=" & Text.From(EntitiesPerPage),

            Url   = BaseUrl & Skip,

            Json  = GetJson(Url),

            Value = Json[#"value"]

        in  Value,

 

    EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),

    PageCount   = Number.RoundUp(EntityCount / EntitiesPerPage),

    PageIndices = { 0 .. PageCount - 1 },

    Pages       = List.Transform(PageIndices, each GetPage(_)),

    Entities    = List.Union(Pages),

    Table       = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in

    Table

 

 

and finaly expende as table

image.png.2c2455bca745edc5a904f9271d03bef2.png

 

.... certainly we have to improve and tick it to make i works better, but I'm newbe in powerBy / Odata ?

Link to comment

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...