Power BI Meets Forms | A Fully Automated Method Using Power Automate And SharePoint
Вставка
- Опубліковано 23 лип 2024
- In today's tutorial, I'd like to share a project involving Microsoft Forms, Power Automate, SharePoint, and of course Power BI.
I was tasked to deliver an end-to-end automation based on a survey that our team has created to survey potential customers using Microsoft Form. However, getting responses out of Form is not that straightforward - at least not in an automated fashion.
It meant that I had to utilize Power Automate to export each response to a SharePoint site.
Once those responses were made available there, picking them up with a Power BI Dataflow and creating a report was a walk in the park...
I hope you like this solution!
Have a good one,
Roland
💪 New videos coming every Tuesday 🤹♂
🤘 New short videos coming every week 👨🏫
🎬 MORE VIDEOS:
Power BI & Power Automate - How to automate reporting process -
• Power BI & Power Autom...
How To Group Field Parameters in Power BI Using DAX - Enhance Report UI - • How To Group Field Par...
Dynamic Row-level Security 🔐 - Based on Dimension Tables - • Dynamic Row-level Secu...
Chapters:
00:00 Topic Of The Day - Automated Forms Reporting
00:46 Intro
00:56 BI-Lingual Analytics
01:15 What To Expect - The Project
02:01 Tools Used - Features
02:32 Microsoft Forms - Survey
03:55 Power Automate - Export Data
06:30 Power Automate - Templates
07:22 Test No1
08:08 Power BI - DataFlow and Report Creation
08:48 Power Automate - DataFlow and Data Refresh
10:08 Test No2 - Go Live
10:48 Summary
11:46 Questions? Comments?
11:58 End
▼▼▼▼▼▼▼▼▼
Make sure to hit the 👍 button and ❗❗ SUBSCRIBE ❗❗ to my channel.
If you have any questions, just let me know either in the comments down below 👇 or send us an e-mail 📩.
▲▲▲▲▲▲▲▲▲
🤝 HOW TO CONNECT 🤝
LinkedIn - / bilingualanalytics
Twitter - / bilanalytics
Subscribe - / @bilingualanalytics
ABOUT ME: bilingualanalytics.com.au/abo...
📍 - Sydney, Australia
📽 GEAR:
📷 - Sony ZV-E10
🎙 - Audio Technica AT2020
🔦 - Neewer 18” Ring Light, Elgato Key Light
🎧 - BOSE QC35
🖥 - ASUS VC279H + Xiaomi Mi Curved 34"
💻 - Intel i7-12700K, Gigabyte Aorus Master 3080 12GB, Corsair Vengeance LPX 64GB, Samsung 980Pro 1TB
Music: www.bensound.com
#PowerBI #PowerAutomate #BILingualAnalytics - Наука та технологія
Have you ever branched out to Power Automate as a Power BI developer? If so, what made you do that? Let me know here 👇👇👇
Very Helpful. Thank you
hello, great video, could you please share the query you paste when adding the Blank query, thank you!!
amazing and refreshing video)
Thanks for the kind words!
Hello Roland Can you answer a question for me? I've uploaded a file with the Image type to an MS Form using an image I selected from my OneDrive. How is the Value column used in the "Create csv table" Action when the Header is an image?
That's a great question. But I guess your answers are not going to be images, right? So survey responders are going to answer a question based on the image. So it should cause trouble
Hi, thank you for the video. Could you achieve a similar result by just having one excel file as your power bi source data, and use power automate action of Add a Row in excel?
Would you still be able to set up automatic data refresh in the power bi report?
Yes, in theory it’s also a way to get the same/similar result.
Hello Roland, thank you very much, i am very prefer all your video. can i ask a question? In MS Form: have field "Rate", but in Action "Create csv table", i forgot input field "Rate". Then, i test some survey okay --> run in Direct PQ, BPI Desktop okay (with 4 columns). After i remember that i miss field "Rate", i add Header & Value for field "Rate" in Action "create csv table" okay --> make a new survey with 5 column. How to Direct PQ update dynamic with 5 columns (some prev responses: blank for 5th column "Rate"? Thanks. i miss some skill in Power query.
Hey, thanks for the kind words!
So yeah, I missed a field while adding those columns in the "create csv table" step :) This is why I suggested testing the full automation before going live.
What I have done is deleted all files from SharePoint and then re-run the test with previous triggers. That essentially moved the dummy files with the right fields back to SharePoint.
I have a form used for inspections which is very complicated with branched options, GPS data, pictures, etc. I currently use Access with its queries and reporting. We're moving towards power automate and want a more robust survey form. Do you think microsoft forms is robust enough for gathering ~100 data points?
It can definitely handle large number of questions. I’m not 100% sure what do you mean by branched option. Are they type/subtype kind of questions?
I would also look into Power Apps as you can develop much more robust applications in it. And Power Apps definitely has “conditional” options.
@@BILingualAnalytics thank you for the suggestion. I will look into it
how to agregate survey respons into one file ? the number of files created could be little too big
The number of files doesn’t really matter as PQ can combine all of them into a single master table. As long as you keep all responses in one location
power automate is not free, even as an extension in power bi?
Unfortunately, no :(