Correct Column Typing in Power BI/Excel with the Via Kondado Schema API

Use the M code below so Power Query honors the data types Kondado actually sends, instead of guessing them by sampling the first rows.

Why this is needed

The standard Power BI/Excel connection through Power Query (described in Power BI Via Kondado Connection and How to Use Power Query in Excel with Via Kondado) loads the CSV directly. When that happens, Power Query tries to guess each column type by sampling the first rows.

That guess can fail in several common cases:

Columns with many empty values — Power Query cannot infer a reliable type.
Columns that look numeric but should be text (e.g., codes with leading zeros, long IDs, ZIP codes, order numbers).
Columns mixing numbers and text in the sample — it may pick number and break later when text appears, or vice versa.
Date/time columns detected as plain text.

Kondado already knows the correct type for every column, because that information is part of our data model. You can fetch this schema directly from our API and apply it in Power Query, ensuring every column lands with the right type.

What you'll need

An active Kondado account with at least one running pipeline.
A Via Kondado destination created on your account.
The Token for your Via Kondado destination (see How to get the token below).
The table name you want to import (see How to get the table name).
Power BI Desktop or Excel 2016+ / Microsoft 365 (with Power Query).

Step-by-step in Power BI (or Excel)

The procedure is identical in both tools — they both use the same engine (Power Query / M language). The only difference is where to find the Advanced Editor button.

1. Open the Power Query Advanced Editor

In Power BI Desktop:

Click Home → Transform Data (opens Power Query).
In Power Query, click Home → New Source → Blank Query.
With the blank query selected, click Home → Advanced Editor.

In Excel:

Go to Data → Get Data → Launch Power Query Editor.
In Power Query, click Home → New Source → Blank Query.
With the blank query selected, click Home → Advanced Editor.

2. Paste the M code below

Replace everything in the Advanced Editor with the code below, then update the two variables at the top (TableName and Token):

let
    // === Parameters — fill in with your values ===
    TableName = "PUT_THE_TABLE_NAME_HERE",
    Token     = "PUT_THE_VIA_KONDADO_TOKEN_HERE",

    // === 1. Load CSV data ===
    CsvUrl = "https://hub.kondado.io/data/" & TableName & "?decimal_sep=dot&token=" & Token,
    Source = Csv.Document(
        Web.Contents(CsvUrl),
        [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]
    ),

    // === 2. Promote the first row as headers ===
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

    // === 3. Load schema (Kondado's official typing) ===
    SchemaUrl      = "https://hub.kondado.io/metadata/schemas/" & TableName & "?decimal_sep=dot&token=" & Token,
    SchemaResponse = Json.Document(Web.Contents(SchemaUrl)),

    // === 4. Map Kondado types to M types ===
    TypeMapping = (col_type as text) as type =>
        if      col_type = "text"      then type text
        else if col_type = "float"     then type number
        else if col_type = "int"       then Int64.Type
        else if col_type = "boolean"   then type logical
        else if col_type = "date"      then type date
        else if col_type = "time"      then type time
        else if col_type = "timestamp" then type datetime
        else type any,

    // === 5. Build {column_name, type} list from schema ===
    ColumnTypes = List.Transform(
        SchemaResponse,
        each {Record.Field(_, "col_name"), TypeMapping(Record.Field(_, "col_type"))}
    ),

    // === 6. Apply the types to the columns ===
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", ColumnTypes)
in
    #"Changed Type"

Important about the decimal separator: the decimal_sep parameter must match in both URLs (data and schema). Use dot for English locales or comma for European/Brazilian locales — but keep it the same on both lines.

3. Finish and load

Click Done in the Advanced Editor.
Power Query will ask for authentication on the two URLs (data and metadata). For both, pick Anonymous and click Connect.
Rename the query to your table name (right-click the query → Rename).
Click Home → Close & Apply (Power BI) or Close & Load (Excel).

Done — your table loads with the correct typing, no longer relying on sampling.

How to get the Via Kondado Token

Go to app.kondado.io and sign in.
In the side menu, click Destinations.
Select your Via Kondado destination.
The token is displayed on the destination details screen — copy it and store it securely.

Heads up: the token grants access to your data. Don't share it publicly, don't commit it to Git, and rotate it immediately if you suspect a leak. For more details on the destination, see Via Kondado.

How to get the table name

In app.kondado.io, go to Pipelines.
Open the pipeline you want.
Click the View Data button.
In the modal, choose the decimal separator and locate the table you want to import.
You can copy the full link (already contains the token and table name) or just note the table name. It appears at the end of the URL: https://hub.kondado.io/data/table_name?token=....

How the type mapping works

The /metadata/schemas/{table} API returns a JSON array shaped like this:

[
  {"col_name": "id",          "col_type": "int"},
  {"col_name": "created_at",  "col_type": "timestamp"},
  {"col_name": "total_value", "col_type": "float"},
  {"col_name": "is_active",   "col_type": "boolean"},
  {"col_name": "description", "col_type": "text"}
]

The M code above translates those Kondado types into native Power Query types:

Kondado type	Power Query (M) type	Notes
`text`	`type text`	Text / string
`int`	`Int64.Type`	64-bit integer
`float`	`type number`	Decimal number
`boolean`	`type logical`	True/False
`date`	`type date`	Date only
`time`	`type time`	Time only
`timestamp`	`type datetime`	Date and time
(anything else)	`type any`	Falls back to Power Query inference

Common questions

Power Query shows an authentication error

Go to File → Options and settings → Data source settings, find the hub.kondado.io URLs, click Edit Permissions, and select Anonymous. The /data/ and /metadata/schemas/ URLs need separate credentials.

Scheduled refresh on Power BI Online doesn't work

Usually it's the "privacy levels" error. See the "Scheduling Automatic Updates in Power BI Online" section in the Power BI Via Kondado Connection article.

Can I parameterize Token and TableName?

Yes — that's the recommended approach for reuse. Create Power Query parameters (Home → Manage Parameters → New Parameter) named TableName and Token, and remove the two declaration lines at the top of the M code. You can then reuse the same template for several tables by just swapping parameters.

Does it work with any table?

Yes — the /metadata/schemas/{table} endpoint exists for every table accessible through Via Kondado. That includes tables from native pipelines and tables generated by your KSQL models.

What if I add a new column later?

Just refresh the query in Power Query (Home → Refresh Preview). Because the schema is fetched dynamically, new columns come in with the correct type automatically.

Next steps

Written by

Thassyo Pereira·Published 2026-04-29