IBM Cloudability

Cloudability

A place for Cloudability product users to learn, connect, share and grow together.

 View Only

  • 1.  Bypassing 64,000 api limit in Power BI

    Posted Tue May 21, 2024 01:28 PM

    Hi community,
    Has anyone figured out a way to bypass the 64000 limits of the cost reporting API in Power BI? I am building a cost report to pull in YTD cost data, but at the moment I have to do one query for each month and combining those queries together. Hope there is a more efficient way to do this. 


    #Cloudability


  • 2.  RE: Bypassing 64,000 api limit in Power BI

    Posted Tue May 21, 2024 02:18 PM

    We had the same issue, and I temporarily solved it by reducing the number of dimensions. That creates less distinct rows.

    The permanent fix would be to use pagination as explained in the Cloudability API documentation, either using limit and offset, or using the next page token. We have not implemented that yet. It seems to require a fair bit of coding skills in Power BI.


    Original Message


  • 3.  RE: Bypassing 64,000 api limit in Power BI

    Posted Wed May 22, 2024 01:23 AM

    The way the Cloudability API do is creating a token for the next  page API response.

    You need to create a loop of your api call within the advance editor of Power BI.

    You can create a function of the api call with url, capture the token, and create loop of the next api call by supplying url&token information.


    Original Message


  • 4.  RE: Bypassing 64,000 api limit in Power BI

    Posted Wed May 22, 2024 02:54 AM

    This is what we did I created a function that makes an initial call and then if there is a value for the next page loop until there isn't to pull the data.



    ------------------------------
    Regards, Jeremy Bryant
    Transport for NSW
    ------------------------------

    Original Message


  • 5.  RE: Bypassing 64,000 api limit in Power BI

    Posted Mon March 17, 2025 11:33 AM

    // function to fetch data from the CDY API as a single page

    _fn_GetPagedData = (Offset as number) =>

              let

                        Source = Json.Document(Web.Contents(_APIUrl & "?dimensions=" & _Dimensions & "&metrics=" & _Metrics & "&start_date=" & _StartDate & "&end_date=" & _EndDate & "&id=" & _ReportId & "&view_id=" & _ViewId & "&limit=" & Text.From(_Limit) & "&offset=" & Text.From(_Offset))),

                        Results = Source[results]

              in

                        Results,

    // Stored each paged results in a list

    PagedResults = List.Generate(

              () => [Offset = 0, Data = _fn_GetPagedData(0)],

              each List.Count([Data]) > 0,

              each [Offset = [Offset] + _Limit, Data = _fn_GetPagedData([Ofset])],

              each [Data]

    )

    // Combine list of paged results into a single table

    PagedResultsTable = List.Combine(PagedResults)

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

    Nb. Each Power Query parameter is denoted by '_' e.g. _APIurl



    ------------------------------
    Ken Cheng
    ------------------------------

    Original Message


  • 6.  RE: Bypassing 64,000 api limit in Power BI

    Posted Tue March 18, 2025 04:19 AM
    Edited by Ken Cheng Tue March 18, 2025 04:23 AM

    PS. After the data has been loaded into Power Query, you should manually set the datatypes then override the default precision (2 decimal places) for certain floating point number fields. Something like this should suffice:

        // Change precision for target columns
        #"Change Precision for Storage" = Table.TransformColumns(#"Changed Type", {"normalised_storage(GB)", each Decimal.Precision(_, 5), type number}),
        #"Change Prevision for Cost" = Table.TransformColumns(#"Change Precision for Storage", {"Cost", each Decimal.Precision(_, 5), type number})



    ------------------------------
    Ken Cheng
    ------------------------------

    Original Message