Verarbeitung von SoSafe-Daten in PowerBI via Power Query M
Dieser Artikel wird bald auch auf Deutsch verfügbar sein!
Introduction
SoSafe customers are able to retrieve analytical/raw data directly from us through the Power BI Integration. However, to provide higher reliability and flexibility, the SoSafe API can also be used through PowerBI.
This guide demonstrates how to use Microsoft Fabric’s Gen 2 Dataflows to retrieve and display data in PowerBI, with examples using the browser version information. It shows how to set up queries for efficient data visualization and future storing through a provided template.
SoSafe PowerBI Template
What is it?
Download here: SoSafe API Power Query Dataflow.pqt
SoSafe has built a template that can be easily imported into PowerBI to retrieve and visualize data from the SoSafe API. This template includes basic functionality on how to retrieve data from the API, process it and create Table structures.

The template only retrieves data within a Gen 2 Dataflow and does not consider further usage into PowerBI reports or data storages.
Using the template
Load it directly into PowerBI by creating a new dataflow and using the file provided above as a model. More information on how to do so in PowerBI can be found here and here.
Add the API Key and Key ID provided through the SoSafe Manager in the two parameters provided by the template:
APIKey
andKeyID
. If you haven’t gotten any of these values, check this guide.Once both values are added, you can then click on the respective tables on the right side of the dataflow to load them one at a time and visualize data:

Done! 🎉 The data is available for you to visualize and process!
Modifying base requests
The template includes three tables for three pieces of data that the SoSafe API provides:
Users: https://support.sosafe.de/ADOC/sosafe-api#SoSafeAPI-Usersendpoint
Campaigns: https://support.sosafe.de/ADOC/sosafe-api#SoSafeAPI-Campaignsendpoint
Campaign Analytics: https://support.sosafe.de/ADOC/sosafe-api#SoSafeAPI-Analyticsendpoint
These endpoints however, contain different kinds of filters for data which can be modified to retrieve different results from the API. Using the Users
table as an example, we can modify the query.
Right-click the
Users
table card and start editing the script.Based on the documentation found in the SoSafe Open API spec, add the corresponding parameters as such and save the script
Done! 🎉
Adding new requests
Adding a new request/table is also quite simple! Before you create your own queries, don’t forget to consult the SoSafe Open API Spec to understand what's possible to retrieve from the API.
Create a new Blank Query and open the script editor.
Inside the
let
block, make use of the performGetRequest function to retrieve JSON dataStill inside the
let
block, use the result of performGetRequest to retrieve the list of records and pass it as an argument to the convertToTable function to convert the JSON data to a Power Query TableDone! 🎉 You will now be able to see your new query next to all the others!
Custom Functions
The template provides two custom functions that can be used throughout the pipeline to make things easier for you.
performGetRequest
This function will call the SoSafe API directly and retrieve data in the JSON format (example responses can be found on the SoSafe Open API Spec). It will also already handle authentication automatically, based on the credentials provided in the first step, as well as add Accept
and Content-Type
as default headers.
Additionally, performGetRequest
also supports retrieving data in chunks, using multiple requests to retrieve the entirety of the data set in smaller pieces. This will improve performance and allow more flexibility and control towards your needs. For example, if chunkBy
and chunkSize
are both passed with example values id
and 5
respectively, and there are 15 values in the id
query parameter, this function will perform 3 requests and return an array of 3 elements containing each response.
This function takes two arguments:
path
(text
): The path of the GET request, for example `/v1/users`options
(nullable record
): The options to pass down this request, they include:headers
(record
): A key/value record of all the headers to pass, excluding default onesparams
(record
): A key/value record of parameters to add to this request. Takes values of typetext
,number
andlist
chunkBy
(text
): The text field to which chunk values by, provide together withchunkSize
and only if the parameter to chunk by is present inparams
chunkSize
(number
): The amount of records to request at a time through the API, provide together withchunkBy
Example request with chunked requests:
responses = performGetRequest(
"/v1/analytics/elearning",
[
params = [ campaignId = Table.Column(Campaigns, "id"),
chunkBy = "campaignId",
chunkSize = 5
]
),
convertToTable
This is a simple function that will convert a given list of records to a Power Query Table. Within the SoSafe API, this means the JSON data of a response retrieved with performGetRequest can be used as argument here. Additionally, it takes an argument to determine what the primary key in the data set to be used in the table is, as well as optional foreign keys. The arguments are as follows:
records
(list
ofrecords
): The list of records to convert to a tabletypes
(list
oflists
): A list with the name of the fields and their respective Power Query types. This will convert the types from the response into proper table types. For example:- CODE
{ { "id", Int64.Type }, { "name", Text.Type } }
primaryKey
(text
): The name of the field that holds the primary key for this tableforeignKey
(nullable list
): An optional list that can be passed with all the names of all the fields that are supposed to be foreign keys
Example conversion dealing with chunked requests:
table = convertToTable(
// convert all responses to a list of records containing the correct data
List.Accumulate(
responses,
{},
(acc, current) => List.Combine({ acc, current[data] })
),
// assign types
{
{ "customerId", Int64.Type },
{ "campaignId", Int64.Type },
{ "campaignName", Text.Type },
{ "firstname", Text.Type },
{ "lastname", Text.Type },
{ "email", Text.Type },
{ "language", Text.Type },
{ "userGroup", Text.Type },
{ "userCreated", Date.Type },
{ "registrationDate", Date.Type },
{ "isActive", Logical.Type },
{ "progress", Percentage.Type },
{ "achievedLevel", Percentage.Type },
{ "allPassed", Text.Type },
{ "passedModules", Int64.Type },
{ "overdueMandatoryModules", Int64.Type },
{ "passedMandatoryModules", Int64.Type },
{ "passedOptionalModules", Int64.Type },
{ "recentlyFinishedModule", Date.Type }
},
// assign primary key
"email"
)