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.

Disclaimer:

Survalyzer product support does not include Power BI support. If you are stuck and you need help, please get in touch with our sales team

Survalyzer API requires a secure POST request with a BEARER Token authentication, unfortunately the Power BI standard dialog for WEB does not offer UI support.
Step 1 to step 4 are therefore necessary to open the advanced query editor of Power BI. In step 5 we configure the connection to Survalyzer Next Generation.

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

Step 1: Create a new project

Step 2: Get data

Step 3: Type “query” and select Blank Query

Step 4: Click on Tab “View” and Click on “Advanced Editor”

Finally we are in the advanced editor to connect with Survalyzer Next Generation

Step 5: Customise the Query / Connect to Survalyzer Next Generation

Important to know: Your Survalyzer account is either hosted in Switzerland or the European Union (Holland). 
for EU: https://api.survalyzer-eu.app
for Switzerland: https://api.survalyzer-swiss.app

Please execute the following steps:

1. Copy the Query sample below: into the advanced query editor of Power BI
2. If you host your data in the EU, pleace replace (twice) https://api.survalyzer-swiss.app with https://api.survalyzer-eu.app.
3. Replace YOUR-TENANT, YOUR-USERNAME, YOUR-PASSWORD in the header
4. in ContentBody replace YOUR-TENANT and SURVEYID
5. customise or remove the condition property (more information about the configuration of the condition property can be found here)

Query Sample:

let
token = Json.Document(Web.Contents("https://api.survalyzer-swiss.app/publicapi/Authentication/v1/GetApiToken",
    [Headers = 
        [#"Content-Type"="application/json;charset=UTF-8" ],
        Content = Text.ToBinary("{tenant:""[YOUR-TENTANT]"", userName:""[YOUR-USERNAME]"", password:""[YOUR-PASSWORD]""}", TextEncoding.Utf8)
    ]
)),
AccessToken = "Bearer " & List.First(Record.ToTable(token)[Value]),

ContentBody="{
    ""tenant"":""[YOUR-TENTANT]"",
    ""surveyId"":""[SURVEYID]"",
    ""conditions"":[
                        {
                            ""conjunction"":""And"",
                            ""identifier"":""State"",
                            ""conditionOperator"":""IsEqualTo"",
                            ""value"":""Completed"",
                            ""conditionType"":""SQL"" 
                        },
                        {
                            ""conjunction"":""Or"",
                            ""identifier"":""State"",
                            ""conditionOperator"":""IsEqualTo"",
                            ""value"":""InProgress"",
                            ""conditionType"":""SQL""
                        }    
                    ],
    ""loadCodePlan"":true                  
}",

surveyData = Json.Document(Web.Contents("
        https://api.survalyzer-swiss.app/publicapi/Interview/v2/ReadInterviewListCompact",
        [Headers=
            [Authorization=AccessToken, #"Content-Type"="application/json;charset=UTF-8"],
            Content = Text.ToBinary(ContentBody)
        ]
)),
SurveyDataTable = Record.ToTable(surveyData)
in
    SurveyDataTable

Step 6: Set the privacy level (if asked)

With these settings, survey data is receivable. Please set the privacy level if asked to do so.

About this page
This documentation uses screenshot from Microsoft Power BI Desktop Version: Version: 2.112.1161.0 64-bit (Dezember 2022).