Easiest Way to Get Microsoft Forms Data in Power BI

Подписаться 71 тыс.

Просмотров 69 тыс.

50% 1

In this Power BI tutorial, you'll learn how to connect directly to Microsoft Forms within Power BI. A lot of other tutorials explain that you need to perform a workaround to get your Forms data, but that's not true anymore! It looks like Microsoft Forms has released an API (maybe unofficial, not sure). We can use this API to get all of our form submission data directly in Power BI. All you need to do is use the built-in Web Connector, meaning the data will be refreshable in both Power BI Desktop and Power BI Service 😁
To enroll in my introductory or advanced Power BI courses:
training.bielite.com/
Blog Post/PBIX Download:
bielite.com/blog/microsoft-fo...
Which Live Trainings would you like to see?
forms.office.com/r/yEuWxLm2H5
Video on Looping in Power Query:
• Easy Looping in Power ...
Elite Power BI Consulting:
bielite.com/
Data Insights Tools:
www.impktful.com/
Connect with me on Twitter!
/ powerbielite
0:00 Intro
0:48 Connecting to Data
3:17 Making a Reusable Function
5:13 Quick Visualizations
6:34 URL Parameter Explanation

Наука

Опубликовано:

1 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 140

@kwaaikraai 3 года назад

Wow! Probably one of the most useful videos on RU-vid right now! Thank you very much, Parker!

@BIElite 3 года назад

Thanks Wilhelm! Glad to hear it.

@scorsellt1229 3 года назад

Oh man this is has to one of the most useful BI videos I have seen for a while, thanks Parker!

@BIElite 3 года назад

Hah, great to hear!

@AujieAlamban 2 года назад

You’re amazing!! Thanks for sharing

@lenzypetty9371 3 года назад

Great video....thanks so much

@PaulBailey3 2 года назад

I wish this was an easier process from MS, but your solution is nice and elegant.

@123batman1000 3 года назад

Thanks for your videos. Passed DA 100 by watching your video and ofcourse practice powerbi daily.

@BIElite 3 года назад

Great to hear that, Sayeed. Congrats!

@yukithejapanese 3 года назад

Great tip!

@bonifacesoh2255 11 месяцев назад

Really insteresting tutorial thank you so much!!

@hobbyhuman9499 3 года назад

Amazing.

@ronnyerpelding9992 2 года назад

Excellent ! And it works also with excel, just add a source like in Power Bi.

@raghukalyan7126 Год назад

Hi, how did you get url (API) in your notepad

@dannileggatt5866 2 года назад

brilliant!

@ThepExcel 3 года назад

Wow! Thx a lot

@BIElite 3 года назад

No problem!

@billshipman8051 3 года назад

Who knew? really nice thank you.

@BIElite 3 года назад

For real! No problem.

@HavensConsulting 3 года назад

Slick Parker! Forms is a fantastic data collection tool, and I never knew you could connect direct via URL. I made a similar video a few years ago connecting forms to a Power BI streaming dataset through Power Automate.

@BIElite 3 года назад

Nice man! Yeah I've been looking for an API endpoint for some time now.

@HavensConsulting 3 года назад

@@BIElite it's a fun way to collect data at trainings and/or presentations. Have everyone take their phone out, snap a QR code on the screen. Then as people fill out the survey they watch in real time as a Power BI report populates data. Gets them more excited about Power BI

@MuhammadBerki 3 года назад

Thanks Parker 👍

@BIElite 3 года назад

No problem, Muhammad!

@rkrishang Год назад

Amazing

@andrekroker420 3 года назад

Awesome!

@BIElite 3 года назад

Agreed!

@chaoweizhong8350 3 года назад

Wonderful！！

@BIElite 3 года назад

For sure!

@branzo79 2 года назад

This is great! Question, if publishing a report to the server (that uses this method) must I update new data submissions from the desktop PBI and republish? Or can I now refresh the report from the server?

@sps425 2 года назад

How do you analyze different forms data kn the graph I.e feedback responses per program comparison?

@hokki28 3 года назад

Very useful! Is there a similar approach für Bookings data?

@yehudabuaron5216 2 года назад

Thank you! I'm using this method for a long time. But suddenly its stoped working and it says that "The OAuth authentication method is not supported for this data source." Do you know something about it?

@kslivkin 2 года назад

Hello, thanks for the video. How to increase the number of responses?

@erinfield1943 2 года назад

Very cool. What if you have the opposite issue though- you want Power BI to generate inspection forms for different inspectors depending on what sites they click on on a map. How could you get those forms e-mailed to the user?

@juliorosales5160 2 года назад

I am wondering, how ca we automate the maxResponseId so it goes up once the list in the form is greater than 1k responses

@bernhardlipka7523 2 года назад

Enjoyed your Video. But how would you connect Power BI to the Forms survey data in Microsoft Teams? Nothing I've tried works.

@gabrielferreiraguimaraes2092 9 месяцев назад

Great tutorial, but I need to know the quiz score in addition to the start time to calculate the duration of the assessment. Is there any way to get this data?

@allonewordlowercase Год назад

Create the Form through teams or sharepoint and you can access a spreadsheet easily connectable in to PBI

@nguyentonhu6710 2 года назад

I got this error when following the your instruction. Can you help what I should do next? "DataFormat.Error: External table is not in the expected format. Details: 8f2adbeb-625c-4126-ac29-8216e40339ed.XLS"

@SaleemAhmad-oh7qh 2 года назад

Does this method still work? I tried for hours but it gives me error. Also the Pbix file has no data.

@richardlindgren5875 Год назад

I'm running into authentication issues when trying. Freely translated the message is "This type of authentication data is not supported by this resource." This is before I even get to give my credentials. Any idea?

@anonname7610 8 месяцев назад

This, it doesnt work

@dannytu3285 8 месяцев назад

Same issue, do we have any solutions?

@fathimasafana9598 5 месяцев назад

Remove :x: from url

@SplatpSs 2 года назад

Can you connect multiple MS forms into a Power BI file?

@gw-j 2 года назад

Not sure if this has been borked by M$ but I had a form that was working perfectly for the last couple of months that yesterday started to generate errors upon refresh. Error was with authentication and now when try to update the authentication with Organizational Account I get the error "The WWW-Authenticate header doesn't contain a valid authorization URl. Header value: 'Bearer'.

@hugopaes1608 2 года назад

The same had happed here, it was working perfectly but today when I tried to refresh I got this error "The WWW-Authenticate header doesn't contain a valid authorization URl. Header value: 'Bearer'." too. Could you please help to reactivate or generate an another valid API? @BI Elite

@Kkacziii 2 года назад

@@hugopaes1608 the same problem

@chukachukwu 2 года назад

Same for me, tearing my head out. Issues is currently impacting 2 of my reports in Production 🤦‍♂🤦‍♂. Has anybody found a fix?

@clairemaynard7279 2 года назад

@@Kkacziii Same problem :(

@samfischer419 2 года назад

Same here :(

@mayobi Год назад

I have an error when I try to connect with my organizational account. "We were unable to connect because this credential type isn't supported for this resource. Please choose another credential type."

@KK-km8xh 5 месяцев назад

I'm getting this now too! how did you fix this at the time?

@mrneveradullmoment 3 месяца назад

Same...

@vivekkoli4479 3 месяца назад

Same here, were you able to find the resolution on this ?

@SteveWinward 3 года назад

Great video! The API URL you show seems to cap the output to 1,000 responses. Is there any way to page through the API to aggregate more than 1,000 responses?

@BIElite 3 года назад

Take a look at my video on looping in Power Query! The link is in the description.

@big3067 2 года назад

just watched this tutorial however connecting via the web has now disabled as of April 2022, are you still able to do it as a direct to form from Power Bi ? I prefer to not create a forms for excel or power automate it to a SP List .

@kateapanovich5639 2 года назад

noooooooo!!! Did you find another way to connect to it via oAuth or something? This solution is soo goood! I also do not want to create a SP list and flow for this. so long for nothing

@cheapdomainnames Год назад

Thanks, I tried. but when signing in with Powerbi Desktop. it comes back with. "We were unable to connect because this credential type isn't supported for this resource. Please choose another credential type." Do you know if this is still valid for the latest powerbi version

@mrneveradullmoment 3 месяца назад

Same...

@liu6896 Месяц назад

can we still find the api now (2024) in Microsoft form?

@kingboo302 2 года назад

I have run into the exact same authentication error that others are mentioning. Glad to see its not just me. But sad to see since this video used to help me so much. the workaround i found that will hopefully help others 1. Open MS Form 2. Click “Responses” tab 3. Click “Open in Excel” 4. Then open the excel file in Desktop Mode 5. Click “File” 6. Click “info” tab 7. Click the button that says “Copy path” 8. Open PowerBI in desktop 9. Click “Get data” 10. Click “Web” 11. Paste the link from Step 7 12. BEFORE clicking “ok”, remove the “?web=1” so the URL ends with .xlsx 13. Click “ok” 14. Click “Table1” or “Form1” not sure what the difference is. Maybe someone can explain to me. 15. Click “Load” 16. And that should be it! Your data is downloaded

@ricecurryman 2 года назад

Unfortunately that would only be a static version of the data, you would only have the data up to the point you downloaded that spreadsheet. My fix was to copy all the existing data to a new spreadsheet and use power automate to append new survey submissions to the new spreadsheet. Use the when a new response is submitted trigger, then get response details, and add a row to table. And use the new spreadsheet as the data source in power bi.

@tnsparks 2 года назад

@@ricecurryman No if the form is saved in a team, then the responses will open in a cloud shared excel file. I have used this method and my responses update when my report auto refreshes.

@kingboo302 2 года назад

@@ricecurryman ah yes that is a good point. i forgot to clarify that my method will only work for those that have shared the data in Teams/Sharepoint where it is automatically collected. do you have a video of your method?

@raphaelqueiroz691 7 месяцев назад

Thanks man 👏👏

@syafiqfathyamermohd6255 3 года назад

Hi, May I know how do you get the file URL? I tried to find but still can't find it.

@BIElite 3 года назад

Hi there, I simply opened up my form and it gives you the form id. Then you can simply plug it into the URL I showed in the video.

@faillerplays Месяц назад

how do i append forms answer to an existing power bi table?

@TraceyOsbornHR 2 года назад

For some reason, the visuals won't refresh, but the data is refreshing. Any ideas?

@stevendavies3934 2 года назад

This has stopped working for me as well. I can no longer access the data in Power Bi

@rumwhiskeyteq8227 2 года назад

@BI Elite Hey does this method automatically update the dataset with new responses?

@dlcolegrove6112 2 года назад

You can setup scheduled refreshes of the data, so it won't be instant updates unless the form was filled out right before a scheduled refresh. However, this method is currently causing issues (sort comments by newest) for those of us that have been using it. We're all getting authentication errors.

@aloulouanis 2 года назад

Thank you very much for this solution. It worked for me very well for 2 months but from last week I have an error (without changing anything!). In power Bi Desktop, it is required to identify again, I choose professionel account as in the video when u add the data for the first time. But the account is not recognised and i have this error: WWW-authenticate header does not contain a valid URI autorisation. header value is: "Bearer". Does someone know how this error can be explained? thanks in advance

@AldoniSinaga Год назад

This is what happen when i try to sign in : We were unable to connect because this credential type isn't supported for this resource. Please choose another credential type. anybody know the solution ?

@bulletkip 2 года назад

goated

@dlcolegrove6112 2 года назад

I used this in December 2021 to make a query that pulls from an in-use form, and then it broke within the past week. It won't authenticate. I still have access to all data and see it, but the code to pull into the dataset is not working because it can't authenticate. I suspect something happened on my organization's network, but I'm curious if anyone else has seen this. Error: The WWW-Authenticate header doesn't contain a valid authorization URI. Header value: 'Bearer'.

@tnsparks 2 года назад

Same problem here and several people below. I have not seen a solution or work around to this yet.

@wunanjun8804 2 года назад

@@tnsparks I got the same problem

@jimihieu 3 года назад

Nice one! Can you do same for MS List?

@BIElite 3 года назад

Hmm, I’ve never used MS Lists before. Looks like they have a legitimate API. I’ll look into a video on this topic.

@jasons6721 Год назад

I'm unable to authenticate. If I paste the link into the browser, it immediately downloads the file, but from Power BI, it won't let me authenticate.

@hugopaes1608 2 года назад

After day 04/05/22 it stopped working. Outdated API I guess. It was working perfectly but today when I tried to refresh my report, I got this error "The WWW-Authenticate header doesn't contain a valid authorization URl. Header value: 'Bearer'." too. Could you please help to reactivate or generate an another valid API? @BI Elite

@clairemaynard7279 2 года назад

I got this error too :(

@dlcolegrove6112 2 года назад

@@clairemaynard7279 Same here. Very curious if anyone figures out a solution.

@Martl1974 2 года назад

for me the same. I've opened a MS Ticket.

@GieBaNa 2 года назад

Same here

@lenzypetty9371 3 года назад

I found Refreshing to be inconsistent. Manual refresh works in Desktop, but not in PowerBI Service

@theogeorgas4232 2 года назад

I get a msg "We were unable to connect because this credential type isn't supported for this resource. Please choose another credential type."

@jamesschuldenfrei2485 Год назад

I'm getting the same - any tips here would be greatly appreciated

@poweruserwithcrashlongjohns Год назад

Since I even found this to be a nice solution vs having to either do a MS Form, Automate, SharePoint combo or just a MS Group Form to have the excel automatically in SharePoint created to tie to BI and having same results as others for some time wanted to post update I found out. MSFT looked into it for me and seems that this was never suppose to work in the first place as designed. Since at some point they fixed the glich this solution is no longer valid. So, back to either a Group MS Form if just needing the data and not doing any intake actions on said data or if one is like a process request then MS Form, Automate, SharePoint combo solution which then BI can tie to ones SPO list. Hope this helps others and spares possible loss in time or fustration. Cheers! 🙂

@brodieleeson4977 11 месяцев назад

Thanks for this, was scratching my head for a solution but I guess its time to revert to my Sharepoint group method.

@martinbubenheimer6289 11 месяцев назад

Seems like MSFT turned off the authentication workflow for the forms API. That means, you cannot log in from Power BI anymore using the Web connector. Now you need to implement a custom connector that handles the login procedure. Therefore, you can also read the list of forms (surveys) from the URL, then load all responses from all forms automatically to Power BI. No more need for changes in Power Query after adding a new form.

@poweruserwithcrashlongjohns 11 месяцев назад

@@martinbubenheimer6289 implementing a custom connector not everyone can do in their organization due to corporate controls/costs associated. For low-code no code the MS Form, Automate, SharePoint list combo for the most part everyone in an organization can create themselves this solution vs a custom connector which would require IT to build.

@martinbubenheimer6289 11 месяцев назад

@@poweruserwithcrashlongjohns Yes, both solutions have their use cases. I'd also recommend to a beginner considering the Power Automate approach as long as the necessary licenses are available.

@loiclevan 2 года назад

The method has worked for months and now the Organizational login is not possible with error message: The WWW-authenticate header doesn't contain a valid authorizatoin URI. Header value: 'bearer'. Has someone experienced this, too?

@pranaysharma8694 2 года назад

Yah I am facing this, were you able to get around this issue?

@hugopaes1608 2 года назад

Me too, got the same error. I'm trying something fresh. Get you know if I achieve something.

@tnsparks 2 года назад

Same problem here, if anyone learns how to fix this please share!

@clairemaynard7279 2 года назад

Same here :(

@tnsparks 2 года назад

I found a workaround solution. It requires the form be part of a group. Then you can pull the excel doc and it should be opened in a sharepoint file. From there you can copy path for the file and input into BI. If new entries are made, you can refresh your BI report and it shows the new entries as well :) Hope this helps everyone else with these issues. PS. only the owner of the form can move it to a group.

@mirrrvelll5164 Год назад

But what if my Forms are not in Excel but directly made in Forms Platform? It is another code and links. Can it be tweaked?

@poweruserwithcrashlongjohns 11 месяцев назад

@mirrrvelll5164 when your form is built within the MS Forms platform if it was created as a Group form your excel file will be in the SharePoint tied to the Group. If its not tied to a group you can utilize Automate to write your MS Form submission to a SharePoint list. Then in Power BI you can connect it to your SharePoint list.

@SanataniAryavrat 3 года назад

This is awesome video. Thank you. Just one suggestion, could you also make a video on how to analyze survey data and create some awesome dashboard presentation?

@BIElite 3 года назад

Hey Manoj, glad you liked it. Thanks for the video recommendation, I’ll look into it!

@jonathanbenitez6772 2 года назад

Worked like a charm for about a month but now mine is not updating due to an authentication error, it looks like it's something with the token needing credentials(?) Anyone know of a fix?

@tnsparks 2 года назад

look below at my comments

@barryc7443 Год назад

@@tnsparks Cant find you comments anywhere are they still valid

@sanjayfenderUtube 2 месяца назад

It's not getting connected

@jlhayes24 2 года назад

Please Acknowledge the problem!!! Is there anything that can be done? Is there anyone looking for a solution? If there is anyone that has fixed issue please share.

@tnsparks 2 года назад

I found a workaround method, its not a fix to this but a different way to enter it. The form has to be saved to a group in Forms first. Then open the responses with the excel cloud icon. Once open then file and info and open desktop app. Then file, info and copy path. You can use this link instead in power bi. This has been working for me so far. Hope it helps

@chukachukwu 2 года назад

I've implemented a solution using excel, one drive and Powerautomate