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.