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),
    #"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.