Extract data from iObeya to Power BI
This getting started documentation provides an overview of how to request data from iObeya to Power BI in order to manipulate it as naturally as a database.
In the big picture of integration use cases, the topic here is the fourth part and more precisely, the possibility to extract data via iObeya APIs.
![tutorial-01-new-to-iobeya@2x Integration Uses Cases - Data from iObeya](https://center.iobeya.com/wp-content/uploads/2022/10/2022-10-18_15h12_33-4.png?vcv-dynamic-field=featured_image&alt=Integration+Uses+Cases+-+Data+from+iObeya&caption=&title=Integration+Uses+Cases+-+Data+from+iObeya)
Note
This kind of integration can be imagined with any system, any tool. This document describes use cases by using Power BI but it is provided as an example only; iObeya is not a Power BI expert and will not maintain and support the code blocks in this document.
1. Pre-requisites
- An iObeya platform with the Facade API deployed
- For online platforms, since the version 4.10, it is automatically managed by iObeya
- For on-premise platforms, this feature is not yet available
- Power BI Online or Power BI Desktop
- An available iObeya JWT Token
- Power BI provides different ways to access an external data source. None of them allows to request an API directly thanks to a JWT Token. So one have to use the Anonymous type and the token will be manually provided in the header of the requests.
![PowerBI Access Web Content PowerBI Access Web Content](https://center.iobeya.com/wp-content/uploads/2023/03/2022-10-11_12h52_49-493x221.png)
Follow the documentation iObeya - Machine to machine requests to the APIs that explains how to get a “JWT Token”
2. Configure a dataset
- In Power BI, Select Get Data > Blank Query
![PowerBi - New Blank Query PowerBi - New Blank Query](https://center.iobeya.com/wp-content/uploads/2023/03/2022-10-18_15h28_36-1-770x435.png)
- Go to “Manage Parameters”
![PowerBi - Manage Parameters PowerBi - Manage Parameters](https://center.iobeya.com/wp-content/uploads/2023/03/2022-10-11_23h23_08-543x174.png)
- Then add 3 new parameters
Name | Description | Value |
JWT Token | The token that owes the iObeya permissions | |
iObeyaBaseUrl | Base URL of the iObeya platform | https://your_platform.iobeya.com or your OnPremise url |
iObeyaApiUrl | Base URL of the Facade API. By default, will be the same as the iObeya base URL + ".api" inserted in the middle | https://your_platform.api.iobeya.com or your OnPremise facade api url |
- Click on “OK”
3. Get data
It is possible to request any services available in the iObeya APIs (Facade and Legacy). Refer to the associated documentation to know more.
Note
Everything in this section required advanced Power BI skills (DAX) to be fully controlled and maintained. iObeya provides this information for illustrative purposes.
The instructions (code blocks) need to be inserted in Power BI via the Advanced editor.
- Right click on a query > Advanced Editor
![PowerBI - Advanced Editor PowerBI - Advanced Editor](https://center.iobeya.com/wp-content/uploads/2023/03/2022-10-12_16h27_30-251x184.png)
- Copy paste a code block provided
![PowerBI - Advanced Query Editor PowerBI - Advanced Query Editor](https://center.iobeya.com/wp-content/uploads/2023/03/2022-10-12_16h29_24-590x314.png)
- Click on "Done"
- You will be ask for credentials, select “Anonymous” as mentioned above.
- Note: If you have a credential error, please check if your token is still valid, or if you missed adding “page & size” on paginated API. This generates a 303 response that Power BI handles as “Invalid credentials” instead of following it.
- Get the results
![PowerBI - Result PowerBI - Result](https://center.iobeya.com/wp-content/uploads/2023/03/2022-10-18_16h56_22-1011x167.png)
3.1 Get the rooms
Get a table with all rooms available to the user associated to the JWT Token:
let
// Request iObeya by giving the JWT Token and expose it as a table
source = Json.Document(Web.Contents(iObeyaApiUrl & "/v1/rooms?page=1&size=50", [Headers=[Authorization="Bearer " & #"JWT Token"]])),
data = source[data],
#"table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"output" = Table.ExpandRecordColumn(#"table", "Column1", {"self", "kind", "id", "name", "domainId"}, {"Column1.self", "Column1.kind", "Column1.id", "Column1.name", "Column1.domainId"})
in
#"output"
Here is an example of the result in a table:
![PowerBI - Results PowerBI - Results](https://center.iobeya.com/wp-content/uploads/2022/10/2022-10-17_11h55_40-1024x406.png)
3.2. Get the rooms - paginated version
Some of the iObeya API endpoints are paginated. In order to retrieve all the pages, it's possible to use such code block in a blank query.
let
// Global method to retrieve all the data from nextlink
iObeya.Feed = (url as text) as table => GetAllPagesByNextLink(url),
// Return a single page
GetPage = (url as text) as table =>
let
Source = Json.Document(Web.Contents(url, [Headers=[Authorization="Bearer " & #"JWT Token"]])),
nextLink = GetNextLink(Source),
data = Table.FromRecords(Source[data])
in
data meta [NextLink = nextLink],
// Read all pages of data.
// After every page, we check the "NextLink" record on the metadata of the previous request.
// Table.GenerateByPage will keep asking for more pages until we return null.
GetAllPagesByNextLink = (url as text) as table =>
Table.GenerateByPage((previous) =>
let
// if previous is null, then this is our first page of data
nextLink = if (previous = null) then url else Value.Metadata(previous)[NextLink]?,
// if NextLink was set to null by the previous call, we know we have no more data
page = if (nextLink <> null) then GetPage(nextLink) else null
in
page
),
// The getNextPage function takes a single argument and is expected to return a nullable table
Table.GenerateByPage = (getNextPage as function) as table =>
let
listOfPages = List.Generate(
() => getNextPage(null), // get the first page of data
(lastPage) => lastPage <> null, // stop when the function returns null
(lastPage) => getNextPage(lastPage) // pass the previous page to the next function call
),
// concatenate the pages together
tableOfPages = Table.FromList(listOfPages, Splitter.SplitByNothing(), {"data"}),
firstRow = tableOfPages{0}?
in
// if we didn't get back any pages of data, return an empty table
// otherwise set the table type based on the columns of the first page
if (firstRow = null) then
Table.FromRows({})
else
Value.ReplaceType(
Table.ExpandTableColumn(tableOfPages, "data", Table.ColumnNames(firstRow[data])),
Value.Type(firstRow[data])
),
// In this implementation, 'response' will be the parsed body of the response after the call to Json.Document.
// Look for the 'next' field and simply return null if it doesn't exist.
GetNextLink = (response) as nullable text => Record.FieldOrDefault(response, "next"),
source = iObeya.Feed(iObeyaAPiUrl & "/v1/rooms/?page=1&size=200")
in
source
- With this, you can handle the pagination automatically
- Change this line with another API call if needed
iObeya.Feed(iObeyaApiUrl & "/v1/rooms/?page=1&size=200")
3.3. Get activity cards on a planning board
Here is an example that gets the activity cards of a planning board placed between two given dates:
let
Source = Json.Document(Web.Contents(iObeya_platform & "/v1/cards/activity?boardId=" & boardId & "&from=" & Date.ToText(StartDatePlanning, "yyyy-MM-dd") & "&to=" & Date.ToText(EndDatePlanning, "yyyy-MM-dd") & "&page=1&size=100", [Headers=[Authorization="Bearer " & #"JWT Token" ]])),
#"Convert in table" = Table.FromRecords({Source}),
#"data developped" = Table.ExpandListColumn(#"Convert in table", "data"),
#"data developped1" = Table.ExpandRecordColumn(#"data developped", "data", {"self", "kind", "id", "title", "startDate", "endDate"}, {"data.self", "data.kind", "data.id", "data.title", "data.startDate", "data.endDate"}),
#"Type modified" = Table.TransformColumnTypes(#"data developped1",{{"self", type text}, {"kind", type text}, {"totalCount", Int64.Type}, {"data.self", type text}, {"data.kind", type text}, {"data.id", type text}, {"data.title", type text}, {"data.startDate", type date}, {"data.endDate", type date}}),
#"Get card_detail" = Table.AddColumn(#"Type modified", "card_details", each card_details([data.id])),
#"Expanded card_details" = Table.ExpandTableColumn(#"Get card_detail", "card_details", {"self", "kind", "id", "title", "style.color", "toolset.name", "toolset.category", "board.self", "board.kind", "board.type", "board.id", "board.name", "description", "startDate", "endDate", "row", "value", "assignees", "checklist"}, {"card_details.self", "card_details.kind", "card_details.id", "card_details.title", "card_details.style.color", "card_details.toolset.name", "card_details.toolset.category", "card_details.board.self", "card_details.board.kind", "card_details.board.type", "card_details.board.id", "card_details.board.name", "card_details.description", "card_details.startDate", "card_details.endDate", "card_details.row", "card_details.value", "card_details.assignees", "card_details.checklist"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded card_details",{{"card_details.row", Int64.Type}})
in
#"Changed Type"
And the custom function card_details used in the previous code block:
= (ID as text) => let
Source = Json.Document(Web.Contents(iObeya_platform & "/v1/cards/" & ID, [Headers=[Authorization="Bearer " & #"JWT Token"]])),
#"Convert in table" = Table.FromRecords({Source}),
#"style developped" = Table.ExpandRecordColumn(#"Convert in table", "style", {"color"}, {"style.color"}),
#"toolset developped" = Table.ExpandRecordColumn(#"style developped", "toolset", {"name", "category"}, {"toolset.name", "toolset.category"}),
#"board developped" = Table.ExpandRecordColumn(#"toolset developped", "board", {"self", "kind", "type", "id", "name"}, {"board.self", "board.kind", "board.type", "board.id", "board.name"}),
#"Type modified" = Table.TransformColumnTypes(#"board developped",{{"self", type text}, {"kind", type text}, {"id", type text}, {"title", type text}, {"style.color", type text}, {"toolset.name", type text}, {"toolset.category", type text}, {"board.self", type text}, {"board.kind", type text}, {"board.type", type text}, {"board.id", type text}, {"board.name", type text}, {"description", type text}, {"startDate", type date}, {"endDate", type date}, {"row", Int64.Type}, {"value", type text}, {"assignees", type any}, {"checklist", type any}})
in
#"Type developped"
Then, by using:
- Activity cards as Tasks
- Rows of the planning as Projects
And a board like this one:
![iObeya Planning board iObeya Planning board](https://center.iobeya.com/wp-content/uploads/2022/10/2022-10-18_10h45_20-1024x421.png)
We are able to build such dashboard that can be refreshed in few seconds based on the content of the corresponding iObeya board.
![PowerBi dashboard from activity cards PowerBi dashboard from activity cards](https://center.iobeya.com/wp-content/uploads/2022/10/2022-10-18_10h38_55-1024x575.png)
Type of integration
Facade API
Tags
Tools
![integration-iobeya-to-powerbi](https://center.iobeya.com/wp-content/uploads/2023/03/integration-iobeya-to-powerbi-1.png)