A Form, a Flow and a Power BI Streaming Dataset Walk Into a Bar - Part 2

In this blog series I will set apart all the steps needed to get this demo up and running on your own. I will use the text in this post, as well as screen shots and video's explaining it step by step. So if you're already (somewhat) familiar with Forms, Power Automate and/or Power BI streaming datasets, feel free to go directly to the video's and start building your own demo right away. Otherwise, keep reading and eventually you'll come across all the resources as well.

In Part 1, I already explained what the reason behind this demo is, and what I used it for in my presentations. I made a start with the demo and showed you how to make a Form and set up a streaming dataset in Power BI.

In Part 2 of this blog series I will show you how to glue it all together with Power Automate and create a report out of the data. There are some things you need to know in the streaming dataset, to properly show the output in your Power BI report. And lastly, I will show you a trick to get your data refreshed live while respondents are answering the questions, but without refreshing your browser!

Create an automation flow

In Power Automate you can create flows manually or with the help of a template. I used a template for my first flow, but if I search for this same template in the gallery it actually doesn't show up.

Power Automate template

I do have the URL because you can check that in your current flows. I resubmitted it as a template because I think it's quite useful:

Power Automate flow details

You can use that template here, or create the steps yourself manually. It's actually not that hard if you take a look at the template.

Create a Power BI report

When the previous steps have been completed, we have built ourselves a working solution, we only have to visualize the results from the dataset. OK, I know, I could also use the default reporting of Microsoft Forms, but that uses pie charts… And you know: every time you create a pie chart, God kills a kitten! :-)

Microsoft Forms default report

Before creating a report it's good to have some data to create the visuals from. To do that, in Forms, clicking on Preview lets you enter the form as a user. You can then create a report by connecting to the streaming dataset, live from Power BI Desktop.

As you saw in the video, I created a basic report with the questions I used in the form. You can create any kind of fancy report with this approach of course.

If you use for example a multiple choice question in your form, with multiple possible answers, I use the DAX formula CONTAINSSTRING or CONTAINSSTRINGEXACT to get the values out of the JSON-string. You write 1 measure per choice and then plot them in a chart. I'm not sure if there's an easier way of doing this, also maybe with a dynamic number of values. Let me know in the comments if you figure this out :-)

Share the Form

Last but not least, we also want to share our form with our audience, right? You can do this by creating a shorter URL with any of the URL shortener services like bit.ly, tinyurl.com or Google. You then share this link in the first few slides of your presentation and even have it visible while attendants are walking in prior to the start of your session. That way they have a minute to fill in the form and you don't have to wait on their answers during the presentation.

Create a Live Dashboard

So the trick for creating a live refreshing view of the answers coming in, is to create a dashboard of the visuals in the Power BI service. So pin all the visuals you need from the report to a dashboard, maybe also add the URL to the form and you're done. You could also refresh the browser every few seconds (either manually or with a browser extension in Chrome). But I do think the automatic refresh in a dashboard looks cleaner, otherwise you're also depending on the speed of the WiFi for loading your whole page every time you refresh.

Recap

As you have seen, setting up this Power Platform demo is actually not that difficult, you just have to know the steps and maybe go through them once to remember them. It does however add a lot of value to a presentation in my opinion. After the PowerPlatform World Tour Meetup in Dublin I had some good feedback from people that wanted to use this setup in their next presentation. From Mary Fealty (B | @Br0adtree) and Marc Lelijveld (B | @MarcLelijveld) I already heard they implemented this, so that's nice to hear!

Mary even added a great feature to the report.

Hey Nicky, used the Forms & Power Automate solution today. Worked a treat. I added a date field so I could filter out my test cases in #PowerBI, there were many. 😊

— Mary Fealty | Broadtree Solutions (@Br0adtree) November 6, 2019

Let me know in the comments or on Twitter if you use this or another method or if you have any questions!

A Form, a Flow and a Power BI Streaming Dataset Walk Into a Bar - Part 1

This is the title of a live demo session that demonstrates the Microsoft Power Platform products working together to create an interactive data application. I recently had the honor of presenting this at the Dublin Power BI User Group (@DublinPUG) during my visit to the Power PlatformWorld Tour in Dublin. The slides of my regular session I gave there can also be found on my GitHub page.

I see you thinking about my title… I know Microsoft Flow is now renamed to PowerAutomate, but then my title just doesn't have the same flow of reading anymore :-)

@NickyvV speaker 4 of 8 at @DublinPUG #PowerPlatformWT
A form, a Flow and a PBI streaming dataset walk into a bar. pic.twitter.com/Jr8WXfJ4EU

— Ben Watt (@benrebooted) October 30, 2019

In this blog series I will set apart all the steps needed to get this demo up and running on your own. I will use the text in this post, as well as screen shots and video's explaining it step by step. So if you're already (somewhat) familiar with Forms, Power Automate and/or Power BI streaming datasets, feel free to go directly to the video's and start building your own demo right away. Otherwise, keep reading and eventually you'll come across all the resources as well.

As this is a blog series, in Part 1 I will explain what the reason behind this demo is, and what I used it for in my presentations. I will make a start with the demo and show you how to make a Form and set up a streaming dataset in Power BI.

Problem

I think it's good to have some form of interactivity in your presentation. Whether it's by (live) polling your audience, telling a story, asking questions during your presentation or any of the other 8 ways to make your presentation more interactive, doesn't really matter.

The way I started doing this during my first presentation was by firing some questions at the audience after I introduced myself. For example, I asked what kind of job they were in at the moment and with what technologies (related to the topic of the presentation) they have worked with. Although that worked pretty well, I was asking closed questions with a show of hands, so if someone didn't participate I couldn't really notice. I wasn't counting hands for that matter. I did get a feel of the audience from my perspective so I know on what topics to focus on a little more in my presentation.

Solution

As we work with the Power Platform suite of products, with my main focus on Power BI, I thought it was a good idea to use some of that Power! So what I did was the following:

• I used Microsoft Forms to create a form,
• Then I set up a Power Automate flow to get the answers,
• Pushed them into a Power BI streaming dataset,
• And finally created a Power BI report and dashboard on that dataset to get a live view of the answers of the audience

Let's go through it step by step.

Create a form

Go over to Microsoft Forms, login and create a new or use an existing form.

I'm going to create a form that I can use in my presentation, so it is focused at the topic I'm presenting on. So I would like the audience to enter their Name, Continent (of origin), Occupation and Experience. Let's see how it works.

A little side-note: unfortunately I can't change the language in Forms to English because this is disabled in our O365-tenant, so some words on the screen are still in Dutch, sorry for that.

I showed a few possible options for creating questions in a form, but you can experiment with other options yourself. Some questions (like the multiple choice answers) are returned as JSON-values so you have to be aware you have to do some calculations in the Power BI report to nicely visualize the values.

Create a streaming dataset

The streaming dataset, of type push, has to be created before selecting the workspace in Power Automate, because otherwise the streaming dataset won't show up. In PowerBI.com you can create a dataset in any workspace, also in My Workspace. You do that by clicking on the +-sign in the home screen of your workspace.

Make sure you turn on the switch for Historic data analysis when creating the dataset. That way Power BI will store the data that's sent through this data stream, and you'll be able to do reporting and analysis on the collected data stream. This switch can also be used to clear the dataset.

Recap

In Part 1 of this blog series I started with Microsoft Forms and a Power BI streaming dataset.

In Part 2 I will show you how to glue it all together with Power Automate and create a report out of the data. There are some tricks you need to know in the streaming dataset to properly show the output in your Power BI report. And lastly I will show you a trick to get your data refreshed live while respondents are answering the questions, but without refreshing your browser!