PowerBI Integration

Create PowerBI reports using live data from Kantree.
To do so, we will use the export api to retreive the data.

Using Kantree as data source

Create a new powerbi report and add a new data source using an empty query.

In the following code fragments, replace:

• DOMAIN: domain name of your kantree installation (eg: kantree.io or company.kantree.io)
• PROJECT_ID: workspace ID

We will first create a function. Copy the following code in your new empty 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"

Then rename the query to “getPage”.

Add a new empty query, click “advanced editor” and copy the following 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),
    #"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” and start editing your report.

Authentication

PowerBI should propose you to authenticate on the new domain automatically.

In the credentials popup, select “Basic” and use your Kantree API Key as the username. Leave password blank.

Sometimes the authentication popup does not appear automatically. In this case, you will need to go to Options and settings > Data source settings > Global permissions. Choose the data source and edit the permissions and finally click Edit under Credentials.