PowerBI Integration

Create PowerBI reports using live data from Kantree.

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.

We will first create a function. Copy the following code in your new empty query:

(Url as text, Page as text) =>
let
    Source = Json.Document(Web.Contents(Url, [ 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
    Domain = "kantree.io",
    ProjectId = "0000",
    
    Url = "https://" & Domain & "/api/1.0/projects/" & ProjectId & "/export/json",
    Source = Json.Document(Web.Contents(Url)),
    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(Url, [Column1])),
    #"Column Names" = Table.ColumnNames(Table.First(#"Invoke Custom Function")[Data]),
    #"Expanded Data" = Table.ExpandTableColumn(#"Invoke Custom Function", "Data", #"Column Names")
in
    #"Expanded Data"

Modify the domain name (if you use private cloud or self hosting) and ProjectId with your project id.

Click “Close & Apply” and start editing your report.