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

PowerBI, how to use Survalyzer API as a data source

Survalyzer offers customers the ability to use PowerBI as an analytics Dashboard. To work with the survey data, several steps are necessary to securely process data.

A pre-requisite for using Survalyzer’s 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 begin, 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 a valid Internet address but not the Survalyzer API. This is because the Survalyzer API requires a POST request, but the dialog only offers a GET request, by default. This would lead to an error message and halt 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 to enter customer-specific information. Please keep in mind that access only works in anonymous mode.

With these settings, survey data is receivable.

The next step is to put the data into a suitable format and create a sophisticated Dashboard with the capabilities of PowerBI.

Limitations
Currently we don’t support hosting of the PowerBI template on PowerBI.com. As previously mentioned, 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 26, 2019

Was this article helpful?

Related Articles