Soon I will have to import data from a website using API calls. I'm mostly going to be importing data from survey results. On the website we are provided an API token and survey ID's and all the necessary information needed to do calls. What I don't know is where to even begin using API calls within Power BI. How exactly is this done? If by chance anyone has any familiarity with the site, it's Qualtrics.
Solved! Go to Solution.
"Great, this is pretty simple and you should be able to set things up quite easily. Since you are only updating your data a couple times per month, I suggest using version 2.5 of the Qualtrics API to update your data. The most recent version is 3, however this version is intended for developers and it requires some advanced knowledge. Version 3 makes it much more difficult to import survey responses into Excel or Power BI. The benefit of using version 3 is that you get an unlimited amount of data requests, whereas with version 2.5 you can max out on your data request if there are too many made in a short period of time. Usually after I make 5-10 requests in a 15 minute window or less, I get an error message saying that I've reach the max amount of requests. Since you are only updating your data a couple times per month, you shouldn't run into this problem. Apologies in advance if you're already aware of some of the instructions I'm about to provide below, hopefully you find it helpful Smiley Happy To start, you're going to want to make sure you have the Qualtrics API available to you. This is an extra service provided by Qualtrics beyond the basic license, so to make sure you have it you'll want to navigate to your Account Setting once you have logged into Qualtrics. You can find your Account Settings by clicking the little icon in the top right corner of your browser. Once you are in your account settings, click on the "Qualtrics IDs" tab. Here you will find all the IDs you need to run API calls. For your case, you will need the ID of the survey you want to pull responses from, it should start with "SV_" followed by a bunch of characters. The other ID you will need is your API token. This is found in a box on the right side of the browser labelled "API". If you don't already have an API token, click the "Generate Token" button. This is a large string of characters that is unique to your account and essentially acts as your username and password which gives a program permission to grab data from your surveys. You'll want to keep it secure and make sure you don't hit the "Generate Token" button again, otherwise you will have to go into all the queries you build a replace your old token with the new one. Once you know where to find the necessary IDs, you can navigate to the Qualtrics API documentation. This is step by step instructions provided by Qualtrics on how to use their various APIs. As mentioned, the most current documentation is for Version 3, which is where you will be taken to if you click on the "API Documentation" link in the Quatlrics IDs tab. For your case, you will want to navigate to the Qualtrics API documentation for version 2.5, found here. You can also type "Qualtrics API documentation 2.5" into google to find it. Once here, scroll down the list of different API calls on the left side of your browser until you locate the "getLegacyResponseData" API (it's located in the Response Requests" section. Click on the link and you will be taken to the section of the API documentation that describes what the "getLegacyResponseData" API call is. I suggest reading through it to familiarize yourself with the different options available to you. At the bottom of the section, there is a "Try it!" link. If you click it, a window will pop up where you can enter the necessary parameters to complete the API call. For now, we will only populate the required parameters. You can populate the additional parameters as you see fit once you have the API working. In the "Required Parameters" section, fill in each of the 4 parameters with the necessary info. In the User field, use the username you use to login to Qualtrics. In token, use the API token we generated in the Qualtrics IDs page. In the format list, I would suggest picking JSON (find it give you the best options when you're querying the data into either Excel or Power BI). In the survey ID field, use the ID from the survey you want to get responses from. Once you have filled out each of the parameters, scroll down to the bottom of the window and click the "Run Request" button. Once clicked, a browser tab will pop up with the "results" of the query. Within this page you will find all your survey responses in a "raw" format. If you get an error, you have likely entered a parameter incorrectly and you will have to go back to check them. You can close the tab with your raw results as they will be of no use to you. Navigate back to window where you entered you parameters and at the bottom, under the "Run Request" button, there is a field labelled "URL:". In this field you will find a URL which you will use in Power BI to import your data. I would suggest either copying this link or saving it in a word doc or notepad file for now. Next, open your Power BI desktop app (it is only possible to connect to your data within the Power BI desktop app. Once connected, you can make build reports and dashboards within the online version, but you will need to set up your data connection in the desktop app). Once open, click on the "Get Data" drop down button located on the ribbon in the "Home" tab. From the drop down options, click on "Web". A window will pop up asking for a URL. Paste the URL you created earlier here. Once pasted, click "OK'. Power BI will then connect to you data, this will take a few moments. Once connected, you will be taken into the Power Query Editor. Click on the "Into Table" button located on the ribbon. This will create a table with two columns. The first column is each respondents Response ID, the second column contains all the data for each respondent. In order to get this data, you will need to expand this second column (it's probably name "Value"). To do this, click on the button found in the column header. This button has two arrows pointing opposite directions from one another. When you click on the button, a selection pane will pop up that will allow you choose which data columns you want to bring into this query. This means that if there is certain data or questions that you won't be reporting on, you can de-select them so they are not included in your data. Select the column you want to include, then and click OK. Within the Power Query Editor, make the necessary changes or edits to you data based on the analysis you want to perform. You'll likely have to change data type, merge columns or replace values, among other things. I'm assuming you will know how to do all this if you've worked withing Power BI before. Once this is complete, you can close the Power Query Editor and start building your dashboards. Hopefully this covered all the necessary steps, let me know if you have any questions."
I've connected to Qualtrics data using Power BI. Depending on which version of the API you are using, you will need to do more than simply copying and pasting the link into the built in Query tool. Feel free to reach out to me if you'd like more help.
@mhaywardm Sweet! I would like to learn how because someone here said exporting survey results is not a desireable thing to do via API calls so I've been exporting results to spreadsheets and pulling the spreadsheets into PBI. Gets repetitive because I have to keep exporting new spreadhseets each month to get new data.