Integration with Power BI, part 2: Using SurveyCTO API

Avatar
Marta Costa
Follow

This is the second part in a series of articles on integrating with Microsoft Power BI. Please read the introduction before reading this part. This part focuses on how to publish data from SurveyCTO into Power BI without using a third party software.

SurveyCTO REST API uses basic authentication (read our comprehensive REST API documentation for details), which is the only authentication supported by Power BI. While you can use a third-party software (e.g. Google Sheets, Microsoft Excel, R, etc.) to import your data into Power BI, you don’t need to rely on any external application to connect SurveyCTO and Power BI.

Note that you still might want to use a third-party service to pull data into Power BI, and all other integration methods are still feasible and relevant. For example, you may use Google Sheets to publish only a subset of your data and, from there, publish only the subset of data into Power BI (as opposed to all of your data by using the direct integration).

 To publish data from your SurveyCTO server into Power B directly, follow these steps:

  1. Open Power BI Desktop and select Get Data from the Introduction Window. Otherwise, go to the Home tab and click on Get Data (select the symbol above, not where it says Get Data with the arrow).
  2. Click on Other and choose Web, then Connect:

getdataweb.png

  1. This will open a window to insert your URL. Here, insert the REST API URL of your form and click Ok:

URL.PNG

  1. A new window will open. Make sure you choose the Basic (authentication) option on the side and then enter your credentials:

credentials.PNG

  1. Click Connect. Your data should start loading at this point and then appear something like this:

binary.png

  1. Click on To Table. A window will open, click Ok.

ToTable.png

  1. Click on the arrows to the right of the column named “Column1”. Here, uncheck the option Use original column name as prefix - this will add unnecessary prefixes to your variable names. If you have an alert with List may be incomplete, click on Load more… and then Ok.

powerbi_loadmore.png

  1. Your dataset will finally appear. Click on Close and Apply, and wait for the changes to apply. Your data is now ready for any Power BI dashboard!

datasetblob.png

  1. All your columns will be formatted as text. Using the Column Tools Tab, you can format them as you like and those transformations will be applied to incoming submissions.
  2. From here, you’ll be able to create Reports and Dashboards using the dataset imported via Google Sheets, published as an Excel workbook. Whenever you want to refresh your tables and graphics so that they’re all illustrating the latest data available on the Server, click on Refresh in the menu bar:

refresh_powerbi.png

  1. Clicking Refresh will refresh the URL link connected, which will get the latest data in your Google Sheet. Your dataset will be updated, as well as all associated graphics and tables. You can learn more about refreshing in these help topics from Microsoft:

There are four other options that we have documented for integrating with Power BI. Click here to view part three of this guide on integration using Excel.

Do you have thoughts on this support article? We'd love to hear them! Feel free to fill out this feedback form.

 

0 Comments

Article is closed for comments.