Power bi retrive the 100 raw with odata

[Jean-Marc Pigeon1709161394]

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.

[Darryl Sakach]

Is there any help for you here: https://docs.citrix.com/en-us/tech-zone/learn/poc-guides/citrix-daas-monitor-powerbi

[Jean-Marc Pigeon1709164078]

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.

[Jean-Marc Pigeon1709164078]

So I create a new query to get my token :

 

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.

 

let

    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

 

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

[Jean-Marc Pigeon1709164078]

I would also recommand to rename the column1 before expende it, it will be better view when you will use relation between table.