Power BI is a Microsoft product that allows you to take tables of data from various sources in order to collate the information together in a Report with a series of tables, charts and cards.
These instructions will enable you to take data from a Softools Application, then using Power BI, build Reports and display these back in Softools.
- Pre-Requisite: Adding a View Report
- Data From Softools into Power BI
- Transform Data - Handling Specific Data Types
- Sharing the Power BI Report and Keeping Data Live
Pre-Requisite: Adding a View Report
This article will focus on best practice integration. It is more optimal to make use of the View API when calling data into the Power BI Model. As such, in order to follow the steps to integrate with Power BI, you must first create a View in the App with the Fields that are needed in the integration
Data Source - From Web - Softools to Power BI
⚠️ - When there are more than 250 Records, use the Paged Data Source Query - ⚠️
The data from a Softools Application can be used in Power BI by making an API Web Call to receive the collection of Records from the App. In this article we will use the View API as this is specifically optimised for data used in third-party applications
First open the Power BI Desktop App and start a new working model.
Note: If you do not have access to Power BI Desktop and are using Power BI Online then create a blank table for the data source and then skip forward to this Power Query compatible version.
Step 1: Click on the Get Data drop down and then select Web to get data from a web source.
Step 2: In the modal that opens, select Advanced and then fill in the following properties to make the data call.
For full information on the View API see the Developer Docs. The key properties to fill in are as follows:
-
URL Parts
- Part 1: The Softools Gateway API - "https://api-gateway.softools.net"
- Part 2: The relative path for the View - "/API/Apps/{{AppID}}/View/{{ViewReportID}}
- Part 3 (Optional): A Query string containing $filter, $top, $skip, $orderby
-
HTTP Request Header Parameters for Authentication
- tenant: The original site host name for your Softools platform. This is usually the first part of your platform URL https://{tenant}.on.softools.net
- apikey: This is the API Key of the User making the call
Note: The Records returned respects Record Security so will be the Records that the User whose API Key is being used has access to in the App. The API Key can only be accessed via the owner of the User Account in My Profile and in order to do this they must have the API Permission. Also note that if the API Key is refreshed it will cause any calls authenticated against the previous API Key to fail until updated to the new key.
Step 3: Click 'OK' to complete the data source set up. If prompted for the Access Level for the Web Content, set this to Anonymous. The Authentication is being handled via the header parameters of 'tenant' and 'apikey'.
Step 4: The result of this is a table of data in Power BI that has been taken from Softools via the View API.
Multiple Web Get Data sources can be added to give data from multiple Apps as well as in conjunction with data sources from other Applications. You can then use table manipulation in Power BI including Convert to Table, Expand Columns, Rename Columns and Setting Data Types. For numeric data ensure the data set has been set to numeric in case cumulative values are used in the Power BI Reports.
Editing the Source Query to allow for Scheduled Refresh
(Also in a Power Query compatible format if not using Power BI Desktop)
When using the Power BI from Web connection as above, it will result in a query in the form:
let
Source =
Json.Document(
Web.Contents(
"https://api-gateway.softools.net/API/Apps/{{AppID}}/View/{{ViewReportID}}",
[
Headers = [
tenant = "{{tenant}}",
apikey = "{{apikey}}"
]
]
)
),
#"Converted to Table" =
Table.FromList(
Source,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
)
in
#"Converted to Table"If we are going to enable Scheduled Refresh of the data in the Power BI model, then in this format, Power BI may complain about authenticating against the full URL.
It would like to authenticate against - https://api-gateway.softools.net/
So, in order to clear up any issue here we split the URL into the main part and relative part.
Step 1: Right click on the Query and select Advanced Editor to see the current Query
Step 2: Replace the query with the following format to split the main and relative parts of the URL so that scheduled refresh can successfully authenticate against the https://api-gateway.softools.net/ root URL. This is also Power Query compatible if not using Power BI Desktop
Note: If starting at this point then you can create a blank query and copy and paste the following directly into the query editor to start a new data source.
let
Source =
Json.Document(
Web.Contents(
"https://api-gateway.softools.net",
[
RelativePath = "API/Apps/{{AppID}}/View/{{ViewReportID}}",
Headers = [
tenant = "{{tenant}}",
apikey = "{{apikey}}"
]
]
)
),
#"Converted to Table" =
Table.FromList(
Source,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
)
in
#"Converted to Table"Place in the {{...}} values for AppID, ViewReportID, tenant and apikey as appropriate for your scenario.
Using Pagination to pull 250+ Records
The View API is limited to 250 Records in one call. When the data set is larger than 250 Records, we need to make multiple calls to pull pages of 250 records and then combine these into one table.
To do this use edit the source query and use the following code:
Note: If starting at this point then you can create a blank query and copy and paste the following directly into the query editor to start a new data source.
let
PageSize = 250,
GetPage = (Skip as number) =>
Json.Document(
Web.Contents(
"https://api-gateway.softools.net",
[
RelativePath = "Api/Apps/{{AppID}}/View/{{ViewReportID}}",
Query = [
#"$skip" = Text.From(Skip),
#"$top" = Text.From(PageSize)
],
Headers = [
tenant = "{{tenant}}",
apikey = "{{apikey}}"
]
]
)
),
Pages =
List.Generate(
() => [Skip = 0, Result = GetPage(0)],
each List.Count([Result]) > 0,
each [Skip = [Skip] + PageSize, Result = GetPage([Skip] + PageSize)],
each [Result]
),
Combined = List.Buffer(List.Combine(Pages)),
#"Converted to Table" =
Table.FromList(
Combined,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
)
in
#"Converted to Table"Replace the {{...}} parts in this code with the appropriate values for your scenario.
Key parts of this code are
PageSize = 250 sets our API calls to be the maximum we can return from one View call
Skip increasing by 250 each time means each call will get the next 250 records.
List.Generate() creates the loop process which will continue until each List.Count([Result]) returns no records
Pages becomes { {Page1Records}, {Page2Records}, {Page3Records}, ...}
Combined = List.Buffer(List.Combine(Pages)) flattens the page results into one set of results
Here is an example call to get a list of Items from an Item App, ordered by Item Name and filtered to Status Red
let
PageSize = 250,
GetPage = (Skip as number) =>
Json.Document(
Web.Contents(
"https://api-gateway.softools.net",
[
RelativePath = "Api/Apps/Items/View/PowerBI",
Query = [
#"$skip" = Text.From(Skip),
#"$top" = Text.From(PageSize),
#"$orderby" = "[ItemName]",
#"$filter" = "[Status] eq 'Red'"
],
Headers = [
tenant = "tenant",
apikey = "*****"
]
]
)
),
Pages =
List.Generate(
() => [Skip = 0, Result = GetPage(0)],
each List.Count([Result]) > 0,
each [Skip = [Skip] + PageSize, Result = GetPage([Skip] + PageSize)],
each [Result]
),
Combined = List.Buffer(List.Combine(Pages)),
#"Converted to Table" =
Table.FromList(
Combined,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
)
in
#"Converted to Table"
BIG DATA - Incremental Refresh/Manual Incremental Pattern
When using pagination to pull data in chunks (for example, 250 records at a time), very large datasets can result in thousands of API calls per refresh. For instance, a dataset of 600,000 records would require 2,400 calls using a 250-record page size, which can be slow and put significant load on the API.
To address this, Power BI Premium users can implement Incremental Refresh, which only refreshes recent data while keeping historical partitions static, drastically reducing API calls and refresh time.
For users without Premium, a manual incremental pattern can achieve a similar effect:
- the full historical dataset is loaded once
- only recent or recently updated records are pulled on a regular schedule
- the results are combined with de-duplication of rows favouring the value in the recently updated table of data.
e.g.
- Load all 600,000 records into a table in Power BI
- Have a table which pulls records updated in the last 30days
- On a monthly basis refresh the data in the full 600,000 row table in Power BI
Both approaches help maintain performance and reduce API load for large datasets. If you are struggling with data source refresh speeds, please reach out to support@softools.net.
Using Date Fields in the Power BI Model
Date Fields via the View API come out as a Key Value pair for '$date' so it is in the format:
"Date":{"$date":1770595200000}The Identifier of our Date Field is 'Date' and the '{"$date":1770595200000}' represents the number of milliseconds since Unix Epoch, which is January 1, 1970, at 00:00:00 UTC.
Suppose we continue with our scenario of pulling Stakeholder Information into Power BI. We may capture the Date that our Stakeholder joined in a Field [DateJoined]
In Power BI this will be represented as a Records for the column values so we will need to manipulate this into date values that we can then use in our reports.
Step 1: Expand the column so that we can see the $date value as a column in the table. This is done by clicking the expand columns icon in the column header. We recommend keeping the original column name as a prefix to identify it when there are multiple date values.
Step 2: Now we have access to the $date value we can create a new column with an expression to convert the Unix Timestamp into a recognisable date value.
Give the new column a recognisable name and set the Data Type to Date. Then use an expression (which also handles when there is no or null date) in the structure:
if [#"DateJoined.$date"] = null then
null
else
#datetime(1970,1,1,0,0,0) + #duration(0,0,0,[#"DateJoined.$date"] / 1000)where [#'DateJoined.$date"] can be replaced by the name of the column in your specific scenario.
Note: An alternative method would be to add an additional Text Field in the App such as [DateJoinedFormatted] with an expression in a Form that when added to the View Report will mean the data is readable as a date in PowerBI such as 2026-01-01.
[DateJoinedFormatted] = if(isValidDate([DateJoined]),
string(year([DateJoined])) + '-' +
if(month([DateJoined]) < 10, '0', '') + string(month([DateJoined])) + '-' +
if(day([DateJoined]) < 10, '0', '') + string(day([DateJoined]))
, '')
Adding Image Fields into a Power BI Table Report
To give your Power BI report a more aesthetically pleasing feel, you can also add images to your Power BI Matrix elements. In the example below we have list of Key Stakeholders. To boost engagement it is nice to be able to put a name to a face, building team connections. In our Softools App, we have a Field to upload a picture of the Stakeholder.
We will need to add this Image Field along with the other data that we need in our Power BI model via a View. This will enable Softools to generate all of the correct backing Fields when the data is parsed to Power BI via the API.
This would be in the form:
https://api-gateway.softools.net/Api/Apps/{AppID}/View/{ViewReportID}
e.g. https://api-gateway.softools.net/Api/Apps/Stakeholder/View/StakholderView
When the data is pulled into your Power BI Data Table, Image Fields will appear with two backing Fields meaning that you have three columns of data in Power BI for each Image Field.
- Field (Stakeholder) This is the Softools database ID for your image
- Field_Link (Stakeholder_Link) This is a publicly accessible link that will display the image in a Power BI report.
- Field_LinkExpiry (Stakeholder_LinkExpiry) When the image link is generated, it will be available to use for a period of three months. To ensure that the images do not disappear from your Power BI Reports after this time enable Scheduled Refresh of Data.
To ensure that the Image is displayed in your Power BI Report and not just the URL, you need to explicitly state the data type for that cell in your Data Table to be Image URL. By doing this Power BI then knows that you are expecting it to display an image and not text.
Build your graphic and use the [Field_Link] property with data category as Image URL and you will now have images from Softools in your Power BI model. Here we can see the thumbnail images of our Stakeholders alongside their key credentials.
Other common scenarios where this feature will add value to your process are
- Documentation & Reporting: Add screenshots or visual evidence in QA, Audit and Issue Log reporting.
- Asset / Product Registers: It helps to have an image of the items that are owned or part of service offerings.
Publishing Power BI to Web to get an Embedded Link
There are two parts to getting the Power BI Report constructed in the Desktop App embedded back into Softools or to another Web location. The first is to Publish the Power BI model to the Power BI Server and get an embedded link that can be used to embed the Report.
Note: If you have built the model directly on Power BI Online then you can skip the stage to publish from Desktop to Power BI and scroll down to step 2.
Step 1: Click on Publish to upload the Power BI Model to the Power BI Server
Step 2: Once this is complete go to Power BI - Data Visualization | Microsoft Power Platform and Sign In.
Step 3: From the Workspace select the Report that you have published to the Power BI Server
Note: There are two options here. The Website or portal will construct an embedded link but still requires users to login to Power BI to see the Report. This is more secure than the second option which is to publish to web. It will generate a URL that anyone with the link can view the Report.
Step 4: Copy the Embedded Link so that you can then use it below in a Dashboard to display in either an App or Homepage
Embedding Power BI Reports into Softools
Create a Dashboard using the Embedded Link
Add a new or edit an existing Dashboard and select one of the tiles to house your Power BI display.
Note: Think about the size of your Power BI display, dedicating a whole dashboard 1x1 (1 Col, 1 Row) will likely give the best results.
From here ensure the i-frame option is selected and copy in the Embedded Link in that you got from Power BI.
Note: The Dashboard tile will require width and height, or else it will display as very small in the UI. We recommend using width as 100% and height as 700(px) as a starting point that you can adjust it from.
Embed the Power BI Dashboard in a Homepage or Report
You will fist need to create the Dashboard like above but once this is done you can add it to a Homepage, where it can then be used at the main point of entry into your site.
Or to an App Level Report to give additional Reporting, perhaps with external sources also feeding in data
Note: To be able to use a Dashboard within a Homepage you must have toggled 'Is Global?' on in the dashboard configuration.
Updating the Web Call for Schedule Refresh of the Data
In Power BI there is the option to use Schedule Refresh for a Web Source in order to pull in the data from the source periodically.
To take advantage of this feature we need to make sure we have edited the API Call for data in order to authenticate properly against 'https://api-gateway.softools.net' through the use of a Relative URL Path.
Step 1: In the Power BI Web App open find the semantic model and click on the refresh option to open the Scheduled Refresh settings
Step 2: Scroll down to the 'Refresh' option and toggle on configuring a refresh schedule.
The frequency of scheduled refreshes allowed will be determined by your Power BI license. However, also factor in that we want to make these integrations as optimal as they can be so if the data sets are large think if they data really does need to be updated more than once a day or once a week. Also, if the data set is very large then consider Big Data Incremental Refresh/Manual Incremental Pattern principles.
Comments
0 comments
Please sign in to leave a comment.