1. Home
  2. Application Programming Interface
  3. PowerBI, how to use Survalyzer API as data source

PowerBI, how to use Survalyzer API as data source

Survalyzer offers customers the possibility to use PowerBI as an analytics Dashboard. To work with the Survey data, several steps are necessary to process data in a secure way.

A prerequisite for using Survalyzers API is the unblocking of the public IP-Address. A step by step instruction is available here: http://education.survalyzer.com/knowledge-base/api-usage-introduction/

To start download the latest version of Power BI Desktop: https://powerbi.microsoft.com/en-us/downloads/

Step by Step instruction: Step 1
Step by Step instruction: Step 2
Step by Step instruction: Step 3
Step by Step instruction: Step 4
Step by Step instruction: Step 5

After installing the tool and creating a new project press “Edit Queries” as shown.

The Query editor opens. Create a new Web query using the context menu:

Add some valid internet address but not use the real Survalyzer API. This is because the Survalyzer API requires a POST request but the dialog only offers GET request per default. This would lead to an error message and stop the process:

The next step is to open the advanced editor from the top menu to modify the connection.

Now replace the content with the following code:

let
body = "{ ""userName"": ""yourUsername"", ""password"": ""yourPassword"" }",
token = Json.Document(Web.Contents(
"https://yoursubdomain.survalyzer.ch/publicapi/Login/GetApiToken",
[Headers = [#"Charset"="UTF-8", #"Content-Type"="application/json"],
Content = Text.ToBinary(body) ])),
AccessTokenTable = Record.ToTable(token),
AccessToken = AccessTokenTable {1}[Value],

body2 = "{ ""surveyId"": ""yoursurveyid"" }",
surveyData = Json.Document(Web.Contents(
"https://yoursubdomain.survalyzer.ch/publicapi/Survey/ReadSurveyData",
[Headers = [#"Charset"="UTF-8", #"Content-Type"="application/json", #"Authorization"="Bearer " & AccessToken], Content = Text.ToBinary(body2) ])),
SurveyDataTable = Record.ToTable(surveyData)
in
SurveyDataTable

Permissions settings

The bold parts of the code are the areas where to enter customer specific information.
Please keep in mind that the access only works in anonymous mode.

With these settings the survey data is receivable. The next step is to transform the data in a suitable format and create a sophisticated dashboard with the possibilities of PowerBI.

Limitations
Currently we don’t support hosting the PowerBI template on PowerBI.com. As mentioned at the beginning the IP-Range must be unblocked. Unfortunately Microsoft doesn’t offer a whitelist with IP-Addresses for PowerBI.com. They only offer a global IP-Whitelist for all services, also containing Azure. Therefore allowing all Microsoft IP-Addresses would jeopardize the effectiveness of this measure.

Updated on August 8, 2019

Was this article helpful?

Related Articles