Sending form submission data to Power BI is made possible by leveraging the Device Magic Database API. After adding this data source, you'll be able to create up-to-date reports and dashboards.
The focus of this article is on accessing a form's submission data, however, the concepts mentioned will apply when connecting to any of Device Magic's other available API's.
Locating the Form ID
In Device Magic, navigate to your Form's page and click the title of the form you'd like to connect. On the following page, select and copy the id from your browser's address bar. You can also retrieve this value by querying the Forms API.
Next, formulate the endpoint to successfully fetch submissions from Device Magic.
https://api.devicemagic.com/api/forms/[form_id]/device_magic_database.json
Ex. https://api.devicemagic.com/api/forms/13328722/device_magic_database.json
Save this URL for later.
Locating your API Key
Select API Access and save the API access token value on the page that follows.
Adding the Data Source in Power BI
On the Home page, click the arrow on “Get data” then click “Web”.
A "From Web" screen will pop up.
1. Select the "Basic" option.
2. Paste the URL from earlier in the relevant section.
3. Click "OK".
Access Web content
Select the Basic tab
Enter your API access token in the User name section
Input any value for the Password
Select
https://api.devicemagic.com/
Select Connect
The form submission data will then be fetched and the Power Query Editor opened upon success. Reference the submissions column for the previously submitted form values. You can now transform and shape your Device Magic submission data.
Images
Images are sent as a link. This link will need to be copy and pasted into a browser to view the image.
Note that in order to view images in PowerBi, the image files will need to be set to be "Public" instead of "Private" within Device Magic. For more info on changing files to be "Public", please see this help doc: Accessing File Storage Settings
Subforms and Repeat Groups
Subforms and Repeat Groups are expandable by clicking the “List” button in the Subforms/Repeat Group’s column.
Their values are not displayed directly in the main spreadsheet.
To learn more about the Device Magic Database API (data structure, available parameters, etc.), please see this article.
For questions or comments, please feel free to contact us at support@devicemagic.com.