Connect Power BI to TWICE Commerce

The Export tab of a report, with the REST API endpoints — Reports > Sales overview > Export

Open in TWICE Admin

reports

Load the revenue and payments ledgers into Power BI as two queries, relate them, and build your own report pages on the rows. Power BI then refreshes the data from TWICE Commerce on the schedule you set.

Prerequisites

Required access: a TWICE Commerce API key. It carries owner-level permissions and sits in the Power BI file, so share the file only with people you would give the key to. See API keys.

Before you start:

Power BI Desktop, and a Power BI service workspace if you want scheduled refresh.
The ledger endpoints and file format from Pull your data into a BI tool. This guide uses them without repeating them.
Your currency code and the first date you want history from.

The walkthrough

Store the key in a parameter

Open Power BI Desktop and select Transform data to open the Power Query editor. Choose Manage Parameters → New Parameter, name it TwiceApiKey, set Type to Text, and paste the key into Current Value.

Add the revenue ledger query

Choose New Source → Blank Query, open Advanced Editor, and replace its contents with the query below. Change currency and FromDate to yours. Rename the query Revenue.The fixed base URL with RelativePath and Query is what lets the Power BI service refresh the query. A URL assembled as one string refreshes in Desktop and fails in the service.

Connect anonymously

Choose Edit Credentials when Power Query asks how to connect, pick Anonymous, and select Connect. The key travels in the header, so no other sign-in applies.

Add the payments ledger query

Duplicate the Revenue query, rename the copy Payments, and change RelativePath to internal/reporting/payments/export. Replace the typed columns with Payment_Date as type date and Amount_Total as type number.

Relate the two tables

Select Close & Apply, then open the Model view. Drag Payments[Revenue_Row_ID] onto Revenue[Row_ID] to create a many-to-one relationship.

Publish and set the credentials in the service

Publish the report to a workspace. In the service, open the semantic model’s Settings, go to Data source credentials, select Edit credentials, and choose Anonymous with the privacy level Organizational. Tick Skip test connection if the dialog offers it.

Schedule the refresh

Turn on Scheduled refresh in the same settings and add the times you want. The source is on the public internet, so no gateway is needed.

Use this query for the revenue ledger:

let
    FromDate = "2025-01-01",
    ToDate = Date.ToText(Date.From(DateTime.LocalNow()), "yyyy-MM-dd"),
    Source = Web.Contents(
        "https://server.twicecommerce.com",
        [
            RelativePath = "internal/reporting/revenue/export",
            Query = [from = FromDate, to = ToDate, currency = "EUR"],
            Headers = [#"X-API-KEY" = TwiceApiKey]
        ]
    ),
    Csv = Csv.Document(Source, [Delimiter = ";", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
    Promoted = Table.PromoteHeaders(Csv, [PromoteAllScalars = true]),
    // A truncated export ends in a notice row with no Row_ID. Drop it.
    DataRows = Table.SelectRows(Promoted, each [Row_ID] <> null and [Row_ID] <> ""),
    Typed = Table.TransformColumnTypes(
        DataRows,
        {
            {"Create_Date", type date}, {"Start_Date", type date}, {"End_Date", type date},
            {"Quantity", type number}, {"Amount_Excl_Tax", type number},
            {"Amount_Total", type number}, {"Amount_Paid", type number},
            {"Amount_Refunded", type number}, {"Amount_Outstanding", type number}
        },
        "en-US"
    )
in
    Typed

The "en-US" culture reads 202.42 as a number on a computer set to a comma-decimal locale, such as Finnish. The tax columns are left untyped, because their number follows the tax rates in the period.

Load more than 100,000 rows

Split the history into one call per year and append the results. Keep it to five years or fewer per refresh, the export rate limit described in Pull your data into a BI tool.

let
    Years = {2024, 2025, 2026},
    GetYear = (year as number) =>
        let
            Source = Web.Contents(
                "https://server.twicecommerce.com",
                [
                    RelativePath = "internal/reporting/revenue/export",
                    Query = [from = Text.From(year) & "-01-01", to = Text.From(year) & "-12-31", currency = "EUR"],
                    Headers = [#"X-API-KEY" = TwiceApiKey]
                ]
            ),
            Csv = Csv.Document(Source, [Delimiter = ";", Encoding = 65001, QuoteStyle = QuoteStyle.Csv])
        in
            Table.PromoteHeaders(Csv, [PromoteAllScalars = true]),
    Combined = Table.Combine(List.Transform(Years, GetYear)),
    DataRows = Table.SelectRows(Combined, each [Row_ID] <> null and [Row_ID] <> "")
in
    DataRows

Add the Typed step from the first query to the end of this one.

How do I know it worked?

The Revenue table shows one row per order line, and Amount_Total is a number column.
A card summing Amount_Total filtered to Started_In_Period = "Yes" matches the Revenue report in the admin for the same period and currency.
The semantic model’s Refresh history in the service shows a completed scheduled refresh.

Troubleshooting / common pitfalls

The service says the dataset includes a dynamic data source

The query builds its URL as one string. Rewrite it with the fixed base URL and the RelativePath and Query options, as in the query above, and publish again.

Amounts load as text, or as 20242 instead of 202.42

The column was typed with your computer’s locale. Keep the "en-US" culture argument in Table.TransformColumnTypes, and delete any Changed Type step Power Query added on its own.

Refresh fails with "The column 'Tax_Rate_2' of the table wasn't found"

A step refers to a tax column the new period does not have. Remove tax columns from every typing or renaming step, and type them in the report layer if you need them.

Refresh fails with 429 Too Many Requests

The refresh made more than five export calls in one minute. Cut the number of yearly calls, or schedule the Revenue and Payments models apart.

Refresh fails with 401 after it worked before

Someone deleted or replaced the API key. Put the new key in the TwiceApiKey parameter, in Desktop or under the semantic model’s Parameters in the service.

Microsoft documents the Web connector and scheduled refresh in its Power Query Web connector and Configure scheduled refresh articles.

Pull your data into a BI tool

The endpoints, the file format and the rate limits.

Connect Excel

The same queries in Excel’s Power Query.

Finance reports

Every ledger column, and how each number is derived.

API keys

Creating and deleting keys.

Launch TWICE

Working in TWICE

How to connect Power BI to TWICE Commerce

Open in TWICE Admin

Prerequisites

The walkthrough

Load more than 100,000 rows

How do I know it worked?

Troubleshooting / common pitfalls

Pull your data into a BI tool

Connect Excel

Finance reports

API keys

Open in TWICE Admin

​Prerequisites

​The walkthrough

​Load more than 100,000 rows

​How do I know it worked?

​Troubleshooting / common pitfalls

​Related articles

Pull your data into a BI tool

Connect Excel

Finance reports

API keys

Prerequisites

The walkthrough

Load more than 100,000 rows

How do I know it worked?

Troubleshooting / common pitfalls

Related articles