Section 1.1: Understanding Business Requirements

Before embarking on the construction of your data collection system, it’s crucial to clarify your business needs rather than jumping in with a random idea. For instance, imagine you're in an office environment and wish to establish a system for regularly distributing surveys among staff.

The first step is identifying the questions you need to ask. This can involve consulting with your supervisor, colleagues, or brainstorming on your own. The second step is to consider the logical flow of the data. In this case, we might want to implement a staff wellness survey that can be completed anonymously. Responses should be directed to a database for subsequent analysis and visualization.

Lastly, determine how different users will interact with your data collection system. For instance, how will your colleagues access the form, and how will management review the results? A helpful technique is to sketch a wireframe on paper to visualize how the form should appear. You can document your requirements on paper and later transfer them into a SharePoint site or a OneNote notebook for relevant stakeholders to access.

Section 1.2: Setting Up a Kanban Board

Kanban boards serve as excellent tools for visualizing project processes while simultaneously functioning as a task list. This is where you can transform your business requirements into actionable tasks.

If you have access to a Kanban board like Microsoft Planner, create the following columns:

• A "To Do" column for all tasks related to form development.
• An "In Progress" column to track tasks you are currently working on.
• A "Done" column for completed tasks.

If Microsoft Planner isn’t available, you could utilize Trello or even a physical board with cards to implement Kanban.

Section 2.1: Building the Form

Since we are using Office 365, Microsoft Forms is a suitable choice for creating your survey. Simply input your questions based on the requirements you've outlined. For simplicity, you can limit yourself to binary questions and an open-ended text field.

Microsoft Forms also supports conditional questions, allowing for follow-up questions based on responses. For example, if someone answers "yes," a related question may appear; otherwise, the form moves on to the next standard question.

Section 2.2: Establishing the Database

While Microsoft Excel is primarily a spreadsheet tool and not a database, it can still serve adequately for smaller data sets. If you're planning to gather extensive data, consider using SharePoint or Access. For our purposes, we will keep things straightforward and use Excel.

To ensure Excel captures data from Microsoft Forms, create a table within your spreadsheet. If you anticipate generating multiple surveys and wish to analyze them collectively in a Power BI report, organizing all your Excel files into a single folder can create your data lake.

This video explains how to use Microsoft Forms for data collection and storage in SharePoint, illustrating the process in a step-by-step manner.

Section 2.3: Automating Data Transfer

The next step is connecting the Form responses to your Excel spreadsheet. While you can manually export data from the form, this approach can be cumbersome. Instead, utilize Power Automate for seamless automation.

Power Automate may seem complex at first, but understanding how to connect Forms to Excel is vital. Below, you'll find a visual workflow to facilitate this integration.

This video demonstrates how to collect data into Excel Online using Office 365 Forms, Surveys, and Questionnaires.

Section 2.4: Conducting Quality Assurance

Prior to sharing your project, it's essential to ensure that everything functions correctly. On an Excel spreadsheet, devise tests to confirm successful outcomes while also identifying potential issues.

For example, a successful outcome would involve users submitting responses only after all required fields are completed. Conversely, an undesirable outcome would be a user's response failing to record in the spreadsheet post-submission. Any failed tests should be documented on your Kanban board for resolution.

Section 3.1: Creating an Analytics Dashboard

With results from your form, you can now link Power BI to your Excel spreadsheet. Your dashboard can be tailored to meet your specific business needs. Power BI simplifies this process: just navigate to your spreadsheet, adjust data types in Power Query, and load your data model.

Within Power BI, you may need to create measures, columns, or manipulate existing objects to facilitate visualizations from the data.

Section 3.2: Quality Assurance for Your Dashboard

Just like your form, it's essential to perform quality assurance on your dashboard. You may find discrepancies in incoming data, necessitating adjustments in the Power BI Query Editor or even modifications to the form itself. For instance, if you prefer checkboxes over dropdowns for a question, this adjustment can enhance data accuracy.

Ensure that any necessary changes are noted on your Kanban board.

Section 3.3: Preparing for Distribution

You cannot simply forward a survey without providing context. It’s important to draft a well-thought-out email explaining the purpose of the survey and its benefits to recipients. This task can be delegated if you prefer.

Section 3.4: Finalizing and Distributing the Survey

Once everything is set, make a copy of your test data spreadsheet, then clear the survey form of test responses. With a clean slate, you can now distribute the form along with an explanation of its purpose to all intended participants.