PowerBI Integration

Create PowerBI reports with the live data of a Kantree workspace.

The simple way: the CSV feed

For small workspaces, use the CSV feed:

1. In the workspace, click Integrations, then Export to spreadsheet & BI tools.
2. Copy the address in Integration with BI tools.
3. In PowerBI, add a Web data source with this address.

For large workspaces (more than 1,000 cards), the feed may fail. Use the API instead, as described below.

Use the Kantree API as a data source

Create a PowerBI report, then add a data source with a blank query.

In the code below, replace:

• DOMAIN with the address of your Kantree (for example kantree.io or company.kantree.io),
• PROJECT_ID with the identifier of the workspace, in double quotes: the part of the address of the workspace after /p/. For example, for https://kantree.io/p/f654c5e7-mobile-app-development, use "f654c5e7-mobile-app-development".

First, create a function. Copy this code in the blank query:

(Path as text, Page as text) =>
let
    Source = Json.Document(Web.Contents("https://DOMAIN", [ RelativePath = Path, Query = [ page = Page ]])),
    #"Converted to Table" = Table.FromRecords(Source[data])
in
    #"Converted to Table"

Rename this query “getPage”.

Add another blank query, click Advanced Editor, then copy this code:

let
    Path = "/api/1.0/projects/" & PROJECT_ID & "/export/json",
    Source = Json.Document(Web.Contents("https://DOMAIN", [ RelativePath = Path ])),
    Pages = {1..Source[total_pages]},
    #"Converted to table" = Table.FromList(Pages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to table",{"Column1", type text}),
    #"Invoke Custom Function" = Table.AddColumn(#"Changed Type", "Data", each getPage(Path, [Column1])),
    #"Column Names" = Table.ColumnNames(Table.First(#"Invoke Custom Function")[Data]),
    #"Expanded Data" = Table.ExpandTableColumn(#"Invoke Custom Function", "Data", #"Column Names")
in
    #"Expanded Data"

Click Close & Apply, then build your report.

Authentication

PowerBI connects with a Kantree API key:

1. In Kantree, open Account settings, then Developer.
2. Click Create API key. Give the key read access to the workspaces that the report uses, and copy the key.
3. When PowerBI asks for credentials for the domain, choose Basic. Enter the API key as the user name, and leave the password empty.

If PowerBI does not ask for credentials, open Options and settings, then Data source settings, then Global permissions. Choose the Kantree data source, click Edit Permissions, then Edit in Credentials.