LIVE Instructor-Led Courses
Dismiss
Transforming Survey Data Insights with Microsoft Forms and Power BI

29 September 2023

Transforming Survey Data Insights with Microsoft Forms and Power BI

Understanding your users and customers is critical for any organisation. But transforming feedback into meaningful insights can be challenging without the right tools. This is where Microsoft Forms and Power BI come in. Together, these Microsoft 365 applications provide a straightforward way to gather survey and questionnaire data, then visualise and analyse it impactfully. 

This article can be used as a resource for our Microsoft Forms Data in Power BI part of our Power BI Training. 

In this hands-on guide, we'll walk through a practical example of using Forms and Power BI in tandem. We'll see how to:

  • Set up a simple survey with Microsoft Forms
  • Connect the Form to Power BI and import the responses
  • Shape and prepare the data for analysis
  • Build reports and interactive dashboards to uncover insights
  • Share and distribute the findings across your organisation

Along the way, we'll explore tips, tricks, and best practices to make the most of these tools for survey analytics and data insights. Let's dive in!

Creating the Survey with Microsoft Forms

Our first step is to set up a basic customer satisfaction survey using Microsoft Forms.

Forms provides an easy drag-and-drop interface for building forms, questionnaires, quizzes and more. Some key features include:

  • Multiple question types - Short text, long text, multiple choice, ratings, rankings and more.
  • Branching logic - Show different questions based on previous answers.
  • Data validation - Require answers or numbers within a specified range.
  • Themes and branding - Tailor the look and feel.
  • Notifications and summary charts - Review responses as they come in.

We'll use these capabilities to create a simple three-question satisfaction survey:

Microsoft Forms survey template

The questions allow customers to rate their overall experience, the product quality, and the customer service on a five-point scale from "Very Unsatisfied" to "Very Satisfied".

Once designed, the Form can be shared via a web link or QR code to start collecting responses. Responses feed into the Form summary charts in real time.

Importing Form Data into Power BI

Now we're ready to bring the data into Power BI for further analysis.

Connecting a Microsoft Form to Power BI is straightforward:

  1. Get the Form ID from the Form settings.
  2. In Power BI, select Get Data and choose Microsoft Forms as the data source.
  3. Enter the Form ID and connect.

This creates a live data connection. As more survey responses are submitted through the Form, Power BI will automatically refresh with the latest data.

Initially, each question and choice from the Form becomes a separate field in Power BI. We'll want to clean this up and shape it into an analysis-friendly format.

Using Power Query, we can:

  • Merge the separate question fields into unified columns
  • Split out choices into separate columns
  • Change column data types (e.g. text questions into categories)
  • Rename columns for readability

After these steps, our data model looks tidy and structured:

Power BI Forms data model

Now we're ready to start reporting and analyzing!

Building Interactive Reports

With the data prepped, we can start building Power BI reports to uncover insights.

Some best practices for structuring Forms data reports:

  • Focus on key questions - Build individual report pages for critical questions.
  • Visualize question summaries - Use donut or column charts to show response distributions.
  • Add drill-downs - Enable drilling from summaries into supporting details.
  • Create question relationships - Interactively filter and compare different questions.
  • Highlight insights - Call out important findings with cards, trends, and synopsis.

Applying these principles, we can build an informative report page for the overall satisfaction question:

Power BI Forms data report

The donut chart summarizes the distribution of responses. Slicers make it easy to filter the data by attributes like location. The large card callouts highlight the % satisfied overall.

Additional report pages can provide similar insights for the other survey questions.

Designing Interactive Dashboards

For broader sharing, we can package key report visuals together into an interactive Power BI dashboard.

Dashboards allow you to combine visualisations from multiple report pages into a single canvas. You can customise layouts while maintaining all the filtering and cross-highlighting functionality.

For our survey data, an effective dashboard might include:

  • Overall satisfaction donut chart
  • Net Promoter Score (NPS) over time
  • Charts for question ratings
  • Slicers to filter by department, location etc.
  • Cards with key metric summaries

With Power BI's responsive mobile-first approach, these dashboards can then be easily accessed on the go from any device.

Sharing and Distributing Insights

Now that we have insightful reports and dashboards built in Power BI, we can publish and share them broadly across our organisation.

Some top options for distributing Forms analytics:

  • Package into apps for mobile access
  • Embed into SharePoint sites and Teams channels
  • Control access with Office groups or security roles
  • Print/export static snapshots as PDFs or images
  • Automate data driven emails and alerts

With a fully built-out solution, users across the company can be empowered with survey insights at their fingertips!

Key Takeaways and What's Next

We've now walked through a complete example of transforming survey data from Microsoft Forms into impactful visual analytics using Power BI.

Some key takeaways:

  • Forms and Power BI offer an integrated way to gather and analyze feedback
  • Connecting a Form directly to Power BI enables automatic live data imports
  • With Power Query, Forms data can be shaped into a model optimized for BI
  • Interactive reports provide question-by-question analysis
  • Dashboards bring everything together into an insights hub

To take things further, we could consider:

  • Enhancing forms with Power Apps for a fuller survey solution
  • Incorporating artificial intelligence for automated sentiment analysis
  • Combining with other data sources (CRM, operational etc.) for broader analytics
  • Establishing refresh schedules and governance processes
  • Building out for broader user enablement and training

Now that you have the fundamentals for integrating Forms and Power BI, you can start supercharging your own survey analytics. Just remember the key steps:

  1. Design effective Forms
  2. Connect to Power BI and import data
  3. Prepare the data model
  4. Build insightful reports
  5. Package into interactive dashboards
  6. Publish and distribute findings

With this framework, you'll be on your way to unlocking the full value of Microsoft Forms and Power BI together for impactful data insights.

Frequently Asked Questions

How do I link my Microsoft Form to Power BI?

In Power BI, select Get Data > Microsoft Forms. Enter the Form ID from the Form Settings to create a connection.

What are some good visualisations for Forms data?

Donut charts work well for question rating distributions. Columns show trends over time. Cards highlight top-line summaries.

Can I customize Forms themes and branding?

Yes, Forms has options to tailor colors, fonts, logos and more to match your branding.

How do I control access to Forms reports in Power BI?

Manage access by sharing Power BI apps or apply row-level security rules based on user attributes.

What are some effective ways to distribute Forms analytics?

Share interactive dashboards, embed into apps and portals, export PDF snapshots, or build automated data-driven alerts.

Next Steps

You might like to read our article on How Top Companies Leverage Microsoft Forms and Power BI for Data-Driven Insights or consider taking a course with one of our leading experts. 

  • Power BI Gain a comprehensive overview of Power BI capabilities. Learn to connect data sources, model and visualize data, and create interactive reports and dashboards.
  • Power BI - Beyond the Basics Take your Power BI skills to the next level with advanced visualization, security and administration features.
  • Power BI - Visualisation Master visual storytelling and effective data visualization design principles in Power BI.
  • Power BI Modelling & Data Prep Learn end-to-end data modeling, shaping and ETL techniques with Power BI Desktop and Power Query.
  • Power BI - Dax Become a DAX expert to analyze and calculate custom measures for impactful Power BI data models.
  • Power BI - Power Query & M Harness the flexible Power Query M language to extract, transform and load data for powerful Power BI analytics.

CONTACT
+44 (0)20 8446 7555

[email protected]

SHARE

 

Copyright © 2023 JBI Training. All Rights Reserved.
JB International Training Ltd  -  Company Registration Number: 08458005
Registered Address: Wohl Enterprise Hub, 2B Redbourne Avenue, London, N3 2BS

Modern Slavery Statement & Corporate Policies | Terms & Conditions | Contact Us

POPULAR

Rust training course                                                                          React training course

Threat modelling training course   Python for data analysts training course

Power BI training course                                   Machine Learning training course

Spring Boot Microservices training course              Terraform training course

Kubernetes training course                                                            C++ training course

Power Automate training course                               Clean Code training course