# Power BI and Excel

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](/cost-reports/explore#export-to-csv-or-excel).

## Before you start

<PlanAvailability feature="ApiTokens" plan="Pro" />

- Creating the token needs the Owner, Integration Owner or Editor organization role; see [API authentication](/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](/currency).

## Create a token

<Stepper>

1. Open **Settings**, then **API tokens**, and select **Create API token**.
2. Enter a **Name** that says where the token is used, such as `Power BI finance report`.
3. Under **Access**, choose the workspace the report reads.
4. Under **Start from**, choose **Read only**, then clear every capability except **View costs**, the only one the query needs.
5. Choose an **Expiration**, such as 90 days, and select **Create API token**.
6. Copy the token from **Your API token**. It is shown only once.

</Stepper>

## The query

Copy the query below, or download [costfluent-costs.pq](https://docs.costfluent.com/power-bi/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.

```powerquery
// 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

<Stepper>

1. On **Home**, open **Get data** and select **Blank query**. The Power Query Editor opens.
2. Select **Advanced Editor**, replace its contents with the query, and select **Done**.
3. When Power BI asks how to connect to `https://api.costfluent.com`, choose **Anonymous** and select **Connect**. The token travels in the query's own `Authorization` header, which Power Query sends only for an anonymous source.
4. Rename the query, such as `Costfluent costs`, and select **Close & Apply**.

</Stepper>

## Load it in Excel

<Stepper>

1. On **Data**, open **Get Data**, then **From Other Sources**, and select **Blank Query**.
2. Select **Advanced Editor**, replace its contents with the query, and select **Done**.
3. When Excel asks how to connect to `https://api.costfluent.com`, choose **Anonymous** and select **Connect**.
4. Select **Close & Load**. The costs land in a table on a new sheet; **Refresh All** on **Data** reads them again.

</Stepper>

:::caution
The token is stored in the query, in plain text, so anyone who opens the `.pbix` or the workbook can read your costs with it. Before you share the file or publish the report, replace the token with a placeholder, or keep the file where only the people allowed to see these costs can open it. When someone who had the file leaves, or the token may have leaked, revoke it under **API tokens** with **Revoke token** and create a new one.
:::

## 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](/concepts/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](/cost-reports/explore#export-to-csv-or-excel) 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](/api/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](https://learn.microsoft.com/en-us/power-query/handling-status-codes)). 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](https://learn.microsoft.com/en-us/power-bi/connect-data/refresh-data)).

:::note
Refresh in the Power BI service is not tested by Costfluent. Microsoft documents that a web source called with a fixed base URL and its `RelativePath` and `Query` options, as this query does, can be refreshed in the service, where a URL built at run time cannot. It does not document the credentials such a query needs there. After publishing, set the source `https://api.costfluent.com` to **Anonymous** under the semantic model's **Data source credentials** and select **Skip test connection**; if the refresh still fails, refresh in Power BI Desktop and publish again.
:::

## Related

- [API authentication](/api/authentication)
- [Public API pagination](/api/pagination)
- [Export to CSV or Excel](/cost-reports/explore#export-to-csv-or-excel)
- [MCP server](/developers/mcp)