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.

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:

https://ona.io/<account_username>/<project_id>/<form_id>

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.

Connect form to PowerBI desktop

Once you identify the form id, launch PowerBI. 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 to the slight right.

In the next window, you will need to enter the API CSV endpoint URL. To enter the URL, select Basic for the authentication and enter the the URL:

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 on 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 instead if the credentials are not correct or the URL is not correct. The data will load as a table as shown in the screenshot below.

Loading Data

After successfully connecting to PowerBI and loaded 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 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 load 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 datasource 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 data source connections already authenticated. Click Clear permissions to clear permissions, and close the window.

Related Articles