Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

API Calls

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.

 

Thanks!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

From @mhaywardm:

 

"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."

View solution in original post

21 REPLIES 21
Anonymous
Not applicable

You can also export all your surveys data to Excel, then filter as needed on PBI. here is a tutorial https://wxwr.medium.com/syncing-qualtrics-survey-results-to-excel-and-powerbi-using-power-automate-f...

ZappySys
Helper I
Helper I

Calling API in Power BI  may be tricky depending on many factors.

  • Authentication Type (Basic, Token Based, Custom 2 Steps , OAuth ... )
  • JSON / XML Structure (one level deep or multiple levels)
  • Do you have to flatten hierarchy (Include parent attributes if you extract nested (e.g. $.Customer.Orders[*].Items[*] ) ?
  • Do you have to worry abount Pagination ?
  • Do you have to worry abount Throttling or other interminant errors?

Many of these discussed in below article.

https://zappysys.com/blog/howto-import-json-rest-api-power-bi/

and here 

https://zappysys.com/blog/tag/power-bi/

 

power-bi-import-soap-api-xml-web-service-advanced-options-sql

 

 

mhaywardm
Frequent Visitor

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.

Hi can you please send me the details as well, since I will need it for my work as well. Thanks

Anonymous
Not applicable

@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.

@Anonymous yes, I've been there 🙂  Thankfully I've developed a way of automating the process using APIs.  I will send you a private message with additional info.

I am not using the same company for survey results but I would be interested to see if what you have done could help me also. Thank you in advance

@mhaywardm Hi, I am also interested in the solution you've developed. I am currenlty dealing with the same issue connecting Power BI to Qualtrics data and would love some help provided regarding the same. 

Anonymous
Not applicable

Hi @rallen1 I've posted what @mhaywardm's original message to me as the accepted solution since I see this has a few others interested as well so he/she doesn't have to keep messaging people individually, Hope it helps!

Thanks @Anonymous!

 

@rallen1if you have any questions, feel free to reach out.

Thanks @Anonymous and @mhaywardm, i will make sure if I need any further help or have any query will reach you for sure.

Anonymous
Not applicable

From @mhaywardm:

 

"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."

The GetLegacyResponseData v 2.5 API is the solution that I used until recently - and it worked really well using especially when I pulled all my surveys into a Power Query in Excel for a consolidated source before pulling into Power BI (that technique helped avoid making too many calls for the data and locking out the account). 

 

However, Qualtrics has now DISABLED that v 2.5 API for all new clients and they plan on DISCONTINUING it in the near future for the existing clients who have been using it (so beware of building something new with this approach).

 

Has anyone successfully pulled their survey response data into Power BI using any of the techniques described on the Qualtrics site New Export API?

OR

Has anyone figured out another (hopefully easier) way to achieve this?

Anonymous
Not applicable

@mhaywardm Hi, I am also interested in the solution you've developed. I am currenlty dealing with the same issue connecting Power BI to Qualtrics data and would love some guidance. 

No problem, I'll send you a private message!

I'm also interested in the solution. Thx, Austin

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂

Great, I'll send you a private message.

Hey, I would also be interested in some additional information on the API calls. Thanks, much appreciated!

Anonymous
Not applicable

Hi @cadam no problem. I've marked the solution to this problem as the answer. Check the first page. Thanks!

Hi, I would also appreciate some furthr input on the API calls! Thanks, much appreciated.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.