Copy page

Developers

Power BI and Excel

Load your Costfluent costs by month, service and sub-account into Power BI Desktop or Excel with a Power Query that reads the Public API and a read-only workspace API token.

The Costfluent query for Power Query reads a workspace's costs from the Public API into a table: one row per month, service and sub-account over the last 12 months, or fewer when the plan keeps less history, with the cost, amortized cost, list cost and currency. Power BI Desktop and Excel both run Power Query, so the same query works in either, and Refresh reads the latest costs again. To take one report's numbers into a spreadsheet once, export it instead; see Export to CSV or Excel.

Before you start

Available on the Pro, Business and Custom plans. Compare plans

• Creating the token needs the Owner, Integration Owner or Editor organization role; see API authentication.
• Power BI Desktop, or Excel for Microsoft 365.
• The workspace's ID: Settings, then Workspaces, lists it in the ID column with a copy button. Its currency settings present the amounts; see Currency settings.

Create a token

The query

Copy the query below, or download costfluent-costs.pq. Replace the three values at the top: ApiToken with your token, WorkspaceId with the workspace's ID, and Months if you want more or fewer than 12 months. Months takes a whole number of 1 or more; any other value stops the query with an error that says so.

Code
// Costfluent costs by month, service and sub-account, read from the Costfluent Public API.
// Paste this into a blank query in Power BI Desktop or Excel, then fill in the three values below.
// https://docs.costfluent.com/developers/power-bi
let
    // A workspace API token made from the "Read only" preset. Anyone holding this file can read
    // your costs with it, so remove it before you share the file.
    ApiToken = "cf_ws_paste_your_token_here",
    // The workspace's ID, from Settings, then Workspaces. Its currency settings present the amounts.
    WorkspaceId = "wsp_paste_the_workspace_id_here",
    // How many months to read, this month included. When the plan keeps less history, the table
    // starts at the first whole month the plan keeps.
    Months = 12,

    BaseUrl = "https://api.costfluent.com",
    PageSize = 5000,
    // Paging reaches 100,000 rows (page x limit); a longer read is split into one window per month.
    MaxRowsPerWindow = 100000,

    Today = Date.From(DateTimeZone.UtcNow()),
    RequestedStart =
        if Months < 1 or Number.RoundDown(Months) <> Months then
            error "Months must be a whole number of 1 or more."
        else
            Date.StartOfMonth(Date.AddMonths(Today, 1 - Months)),
    IsoDate = (value as date) as text => Date.ToText(value, [Format = "yyyy-MM-dd", Culture = "en-US"]),

    ReadPage = (startDate as date, endDate as date, page as number) as record =>
        Json.Document(
            Web.Contents(
                BaseUrl,
                [
                    RelativePath = "v1/costs",
                    Query = [
                        startDate = IsoDate(startDate),
                        endDate = IsoDate(endDate),
                        workspaceId = WorkspaceId,
                        granularity = "month",
                        groupBy = {"serviceName", "subAccount"},
                        page = Number.ToText(page),
                        limit = Number.ToText(PageSize)
                    ],
                    Headers = [Authorization = "Bearer " & ApiToken, Accept = "application/json"]
                ]
            )
        ),

    // Reads every page of one window, following links.next until it is null.
    ReadWindow = (startDate as date, endDate as date) as list =>
        List.Combine(
            List.Generate(
                () => [Page = 1, Response = ReadPage(startDate, endDate, 1)],
                each [Response] <> null,
                each [
                    Page = [Page] + 1,
                    Response = if [Response][links][next]? = null then null else ReadPage(startDate, endDate, [Page] + 1)
                ],
                each [Response][data]
            )
        ),

    // The whole window's first page tells how many rows it holds, and its meta.historyFloor the
    // earliest day the plan keeps when that is later than the requested start. The table then starts
    // at the first whole month from that day, so no month shows only part of its cost. When the start
    // stays, ReadWindow's first page is the same request, which Power Query may answer from its cache.
    WholeWindow = ReadPage(RequestedStart, Today, 1),
    HistoryFloor = WholeWindow[meta][historyFloor]?,
    FloorDay = if HistoryFloor = null then null else Date.FromText(HistoryFloor, [Format = "yyyy-MM-dd", Culture = "en-US"]),
    FirstDay =
        if FloorDay = null then
            RequestedStart
        else if Date.Day(FloorDay) = 1 then
            FloorDay
        else
            Date.AddMonths(Date.StartOfMonth(FloorDay), 1),
    MonthCount = (Date.Year(Today) - Date.Year(FirstDay)) * 12 + Date.Month(Today) - Date.Month(FirstDay) + 1,
    Windows =
        if WholeWindow[meta][totalRecords] <= MaxRowsPerWindow then
            {[Start = FirstDay, End = Today]}
        else
            List.Transform(
                {0 .. MonthCount - 1},
                each [
                    Start = Date.AddMonths(FirstDay, _),
                    End = List.Min({Date.EndOfMonth(Date.AddMonths(FirstDay, _)), Today})
                ]
            ),
    Rows = List.Combine(List.Transform(Windows, each ReadWindow([Start], [End]))),

    // Amounts arrive as decimal strings so that no digit is lost on the way.
    Costs = Table.FromRecords(
        List.Transform(
            Rows,
            each [
                Month = Date.FromText([date], [Format = "yyyy-MM-dd", Culture = "en-US"]),
                Service = [dimensions][ServiceName]?,
                #"Sub-account" = [dimensions][SubAccount]?,
                Cost = Decimal.From([cost], "en-US"),
                #"Amortized cost" = Decimal.From([amortizedCost], "en-US"),
                #"List cost" = Decimal.From([listCost], "en-US"),
                Currency = [currency]
            ]
        ),
        type table [
            Month = date,
            Service = nullable text,
            #"Sub-account" = nullable text,
            Cost = number,
            #"Amortized cost" = number,
            #"List cost" = number,
            Currency = text
        ]
    )
in
    Costs

Load it in Power BI Desktop

Load it in Excel

How it behaves

• Each row is one month, service and sub-account, with Cost, Amortized cost and List cost as numbers and Currency beside them. The query reads with the Public API's defaults: amortized, refunds and tax included, credits left out; see Cost amounts. A month, service and sub-account with no cost data has no row: a missing row means Costfluent holds no data for it, not a cost of zero. The current month runs only to the latest day the providers have delivered.
• The plan's history limit applies; the plan marker above links to each plan's limits. When the plan keeps fewer months than Months asks for, the API reports the earliest day it keeps, and the query starts at the first whole month from that day, so no month shows only part of its cost. The earliest month in the Month column shows where the plan's history begins.
• The API sends every amount as an exact decimal. Power BI and Excel hold a number with 15 significant digits, so an amount with more digits is rounded in its last places; the CSV export is exact.
• One read reaches 100,000 rows, 20 pages of 5,000. When the whole window holds more, the query reads it one month at a time. A single month with more than 100,000 rows fails with the API's error; remove "subAccount" from groupBy to need fewer rows, which leaves the Sub-account column empty.
• Each refresh sends one request to learn how many rows the period holds, then one per page of up to 5,000 rows, with at least one for the whole period, or at least one per month when the read is split. When the plan's history does not move the start, the first page repeats that first request, which Power Query may answer from its cache. Every request counts against the token's limits; see Rate limits.
• When the API answers 429 because the token reached its rate limit, Power Query tries the request again up to three times, each time after the seconds the API's Retry-After header names, as Microsoft documents for Web.Contents (Handle status codes with Web.Contents). A refresh that reaches the rate limit therefore slows down. A request the API still refuses after that, or refuses with another error, fails the refresh with the API's error, and the next refresh tries again.
• An expired or revoked token makes the API answer 401, which Power Query reports as a credentials error and may answer by asking for credentials. Changing the credentials there does not help, because the token travels in the query: create a new token and replace it in the query.
• Microsoft limits refreshes in the Power BI service: up to 8 scheduled refreshes a day with Power BI Pro and 48 with Premium, Premium Per User or Fabric capacity, each finishing within two hours on shared capacity, and a schedule pauses after two months without a visit to the report (Data refresh in Power BI).

Related

• API authentication
• Public API pagination
• Export to CSV or Excel
• MCP server