---
slug: power-bi-und-excel
title: "How do I get my figures into Power BI or Excel?"
category: berichte
locale: en
updated: 2026-10-15
keywords: ["Power BI","Excel","Power Query","query","template","parameter","read API","refresh"]
questions: ["Can I load my figures into Power BI or Excel and refresh them there?","Connect Power BI","Load Metrunio figures into Excel","Is there a Power Query template?","Why does Power BI ask me to sign in?","Scheduled refresh in the Power BI service","How do I refresh the figures in Excel?"]
url: https://bench.metrunio.com/en/help/power-bi-und-excel
---

# How do I get my figures into Power BI or Excel?

**Can I load my figures into Power BI or Excel and refresh them there?**

With a Power Query query that loads your daily rows per channel and campaign through the read API; Power BI and Excel both understand the same query. You create the read key as a parameter, and the query sends it in the request header, never in the address. The read API comes with Growth, Scale and the agency plan.

Power BI and Excel fetch data through Power Query, and both understand the same query, written in the M language. The template below loads your account’s daily rows as a table with suitable column types. After that you refresh it like any other data source.

## How to set it up

1. In the portal, create a read key under “Exports & AI connections” in “API access”, ideally one just for this file.
2. Open the Power Query editor with a blank query: in Excel via “Data > Get Data > From Other Sources > Blank Query”, in Power BI Desktop via “Home > Get data > Blank query”.
3. Create the parameter MetrunioKey of type “Text” via “Home > Manage Parameters > New Parameter” and enter your key as its current value.
4. Select the blank query, click “Advanced Editor”, replace its contents with the template below and confirm with “Done”.
5. If Power Query asks how to access the web content, choose “Anonymous”. The query sends the key itself in the Authorization header, and Power Query only accepts that header with this kind of access.
6. Load the table with “Close & Load” in Excel or “Close & Apply” in Power BI.

## The template

```powerquery
// Metrunio: daily rows per channel and campaign, the last 30 days up to today.
// The key comes from the parameter MetrunioKey and never appears in the address.
let
    Response = Json.Document(
        Web.Contents(
            "https://api.metrunio.com/v1/data/daily",
            [Headers = [Authorization = "Bearer " & MetrunioKey, #"Accept-Language" = "en"]]
        )
    ),
    Rows = Table.FromRecords(
        Response[data],
        {
            "day", "channelName", "campaignName",
            "attributedConversions", "attributedRevenue", "postViewConversions",
            "unattributedConversions", "unattributedRevenue",
            "clicks", "impressions", "viewableImpressions", "cost"
        },
        MissingField.UseNull
    ),
    Typed = Table.TransformColumnTypes(Rows, {
        {"day", type date}, {"channelName", type text}, {"campaignName", type text},
        {"attributedConversions", type number}, {"attributedRevenue", type number},
        {"postViewConversions", type number}, {"unattributedConversions", type number},
        {"unattributedRevenue", type number}, {"clicks", Int64.Type},
        {"impressions", Int64.Type}, {"viewableImpressions", Int64.Type},
        {"cost", type number}
    }),
    Named = Table.RenameColumns(Typed, {
        {"day", "Day"}, {"channelName", "Channel"}, {"campaignName", "Campaign"},
        {"attributedConversions", "Attributed conversions"},
        {"attributedRevenue", "Attributed revenue"},
        {"postViewConversions", "Post-view conversions"},
        {"unattributedConversions", "Unattributed conversions"},
        {"unattributedRevenue", "Unattributed revenue"},
        {"clicks", "Clicks"}, {"impressions", "Impressions"},
        {"viewableImpressions", "Viewable impressions"}, {"cost", "Cost"}
    })
in
    Named
```

## What the table contains

One row per day, channel and campaign for the last 30 days up to and including today, counted in your account’s time zone. Attributed conversions and attributed revenue follow your account’s default model, like the reports; clicks and impressions are measured. If a row’s cost stays empty, no cost was recorded for it. That is not the same as zero. Unattributed conversions sit in the row without channel and campaign.

## Refreshing

- Excel: “Data > Refresh All” fetches the figures again. In the query properties you can have Excel refresh when the file opens.
- Power BI Desktop: “Refresh” on the Home ribbon.
- Power BI service: for scheduled refresh, set the data source credentials to “Anonymous” and tick “Skip test connection”. Otherwise the service tests the connection without the key and gets a refusal.

## Adjusting it

- Period and model: in the Web.Contents call, add for example Query = [from = "2026-01-01", model = "linear"] next to Headers. Without an end, the period runs up to today; without a model, your account’s default model applies.
- A query returns at most 50,000 rows. If it needs more, the API refuses instead of dropping rows; a shorter period helps.
- When the API refuses, Power Query shows the status number. 401 means the key is missing, incomplete, revoked or expired. 403 means your plan does not include the read API or the account is not active. 422 means the period has too many rows or a setting is wrong.

> **Caution:** The key is stored as a parameter in the file. Whoever gets the file gets the key, including anyone allowed to download a report from the Power BI service. Only pass the file to people who may know the key, or delete the parameter’s value first.

> **Note:** In Excel and Power BI Desktop the figures stay in your file. If you publish the report to the Power BI service, they go to Microsoft and are processed under the terms of your contract with Microsoft. The rows are sums per day, channel and campaign and contain no identifiers of individual people.

## Also asked

- Connect Power BI
- Load Metrunio figures into Excel
- Is there a Power Query template?
- Why does Power BI ask me to sign in?
- Scheduled refresh in the Power BI service
- How do I refresh the figures in Excel?

## Related

- https://bench.metrunio.com/en/help/google-sheets
- https://bench.metrunio.com/en/help/csv-export
- https://bench.metrunio.com/en/help/mcp-anbinden
- https://bench.metrunio.com/en/help/api-keys-rotieren
- https://bench.metrunio.com/en/help/tagesgrenzen-und-zeitzone
- https://bench.metrunio.com/en/help/attributionsmodelle