1. Home
  2. Analyzing Data
  3. Data Visualization
  4. Connect a form to PowerBi using the API
  1. Home
  2. Analyzing Data
  3. Connect a form to PowerBi using the API

Connect a form to PowerBi using the API

After collecting and submitting data to Ona Data, you may want to simplify data analysis and empower key decision-makers by creating a dashboard. You can create dashboards easily using visualization software, like Tableau or PowerBI. To push data into PowerBI from Ona Data, you can use the API.  To use API, you need a Pro plan or higher account. See the plans page for information on the different plans and this guide on how to upgrade your account.

There are two ways to push data from an Ona Data form to Power BI:

  1. Your Ona Data username and password
  2. Your API key

Before connecting your Ona Data form to PowerBI, you will need a form id and possibly, an API token.

Get Form ID

To connect data from an Ona Data form to PowerBi, you need the form id. To find the form id, log into Ona Data and navigate to the form’s overview page. The form id is the last part of the form’s overview page URL:

In the example below, the red arrow highlights the forms’ URL location, and the red box highlights the form id. In this case, the form id is 330364.

Get API Key

Each Ona Data account has an API key to help you access the data via the Ona Data API. See our API documentation for more information on the API capabilities. To access this key, go to your account settings page, and click on the API tab.

You will need to confirm your password to access the API key.  Put in your Ona Data account password, and click on Confirm password. Then, your API key will appear, which is a string of alphanumeric characters. Copy it and keep it safe somewhere.

Caution: Do NOT share this API key with anyone who you do not want to have access to your data.

Connect the form to PowerBI desktop

Once you have identified the form id and have the API key, launch PowerBI and connect using either method.

Option 1: Using a username and password configuration

From the welcome screen, select Get Data, as shown below.

Another window will appear with a search bar. Type ‘web’, and select Web in the list.

In the next window, enter the API CSV endpoint URL. To enter the URL, select Basic for the authentication type and enter the URL, similar to the one below.

https://api.ona.io/api/v1/data/<form_id>.csv

Replace the <form_id> with your form id, as found previously, and click OK.

The next page will require you to enter your authentication credentials. Select the Basic section, and enter your Ona Data username and password. Click Connect to connect to your account and pull data from the desired form.

After connecting your account, a connection will be established. There will be an authentication error if your username/password combination or URL is not correct. The data will load as a table, as shown below.

Option 2: Using the API key

This method can be used by users who do not authenticate using their account username and password. Once you have selected Web as your data source, choose the Advanced option and put in the same URL used in the first method under the URL Parts section. It should also appear under the URL Preview section as you type it in.

Under HTTP request header parameters (optional), type in the word “Authorization” on the first dropdown menu on the left. For the text box on the right, type in the word “key” followed by the API key, we copied earlier, with a space in between.

The next page will require you to enter your authentication credentials. Select the Anonymous section and leave everything else as default. Click on Connect to connect to your account and pull data from the desired form.

Loading Data

After successfully connecting to PowerBI and loading the table, click the Load button to load data and add a table to PowerBI, as shown below.

Start Creating Visualizations in PowerBI

After loading the data, the table will be created in PowerBI with the form ID as the table name, near the fields section.

Click on the table drop-down, and all columns will open allowing you to start creating charts.

Transform/Clean Data

To transform/clean data, click Transform, and a power query editor will open with a table as shown below. Read more on how to transform data in this guide.

If you have a repeat group(s) in your form, it will be stored as a list, and you will need to expand the repeat group(s) as extra rows as shown below.

Once you are satisfied with the data structure, data types and column heading name, click Apply & Close.  The query will create a table, which you can use to generate charts and tables. 

Refresh Data in PowerBI

Once charts are published and data submitted on Ona Data, you only need to click Refresh to get the latest data. 

For more information on creating charts and dashboards in PowerBI, please see this help documentation

Clear permission

After authenticating the first time prior to loading or transforming/cleaning data, you will not need to authenticate again. After you have entered the URL, it will automatically connect using the credentials entered earlier. If you need to connect to another account, you will need to clear the data source permission settings allowing you to authenticate again. To clear permissions, click on File, select Options and Settings, and then Data source settings.

A data source settings page will open showing all authenticated data source connections. Click Clear permissions to clear permissions, and close the window.

Related Articles