- Yooo! What's up? This is Patrick from Guy in
a Cube and in this video, I'm gonna show you how
to use Power BI desktop, to go and interact with the data that you collect from Microsoft Forms. Stay tuned. (upbeat music) If you're finding this
for the very first time, be sure to hit that subscribe button to stay up to date from all the videos from both Adam and this guy. I've been thinking about
doing this video for a while and then recently during a live stream, someone said, well, how do I connect to the
data in Microsoft Forms? And I explained it and he said, "Patrick you just blew my mind." And I said, I should do a video on this because the questions keep coming up. So I decided to do a video. There's gonna be two paths, right? There's gonna be two paths. You guys know I always like
to take different paths. One, you're going to
use forms.microsoft.com and the other one you're gonna use SharePoint or OneDrive. So enough of all this talking, you guys know I like to do, let's do what? Let's head over to my laptop. There's a couple of ways
you can create your form. You can start by going
to forms.microsoft.com and then you can go here
and create a new form. And so that's actually what I've done with this Power BI survey. So if you click it and
you can respond to it but the challenge is, is
how do I get to that data? Well, the only way that I
figured out how to do it, was to click on responses and
you can open this in Excel and it's gonna download the file. But I have to do this every time I wanna update my data in Power BI. And I know what you guys thinking, Patrick, you're not lazy,
you're just really efficient and this is not efficient. So you're telling me every
time I wanna, you know, refresh my report, I gotta go here, go to responses, open the Excel file, refresh my Power BI report and then publish it or maybe
store this out on a Share and then, you know, have my
schedule refresh (indistinct), some process I gotta write. That is not something
I wanna do every time. I don't wanna maintain the (indistinct). Cause guess what? I'm probably gonna forget. So this is path one, right? Using forms.microsoft.com. Like I said you can use
SharePoint online or OneDrive and do this in a more efficient way. And so that's path number two. Let me show you. You can do this one of two ways. You can go to SharePoint, you can see guyinacube.sharepoint.com. And you can see all of my, you know, the groups that I have created. So you create your group and
I'm following lots of groups. But you create a group, and
here's my group right here. And what you would do
is you go to documents and then right at the top, you
see where it says this plus, click it and you'll see forms for Excel. You're thinking Patrick, well,
I just wanna create a form. Hang on, hang on, just
calm down one second. Or you can go to OneDrive and again it's gotta be a
shared library or a group and you can do the exact same thing. So this is the exact same shared library or group that I created. It's called Surveys. And you can see this
Excel file appears here. Watch this, you can do either approach, if you do it in SharePoint
or if you do it in OneDrive the result is gonna be the same. So if I click this drop down right here choose forms for Excel, and
I'm going to click create. What do you think is gonna happen? Boom! It takes me to forms and I start building my form. The difference is if I go back
over to my OneDrive folder, you'll see that there's a new Excel file. If I go and refresh this page right here you can see there's a
new Excel file, right? It automatically updates it for me. If I go over to forms.microsoft.com and you'll see a new survey is created. So it's all integrated
across these platforms. The advantage though, the advantage of using SharePoint online or OneDrive to create this, and you guys may have noticed
this when I was doing this, is that it creates this Excel file where it's actually gonna store the data. Now, can you get the path to that file? Unlike if you do it
with forms.microsoft.com is I couldn't figure out
how to get the path to it. I couldn't find out where it was stored and maybe there's some
genius forms person out there who's gonna post in the comments and go, "Oh, silly Patrick,
this is how you do it." But if not, if not, you can
use this approach, right? If you can't do it with forms you can use the approach I'm showing you. Let me show you. So what you would do is, you can go here either in
SharePoint or in OneDrive, find the file. So I'm gonna use one of a survey
that I've already created. We're gonna go ahead and
say open this in the app. So let's open it in
Excel and then you'll see there's all the data
that I've already entered for the survey. What you wanna do is go to file, choose info and click copy path and be sure to close the file. Let's go ahead and close it and then open up power BI desktop. And like I explained in the video if you choose to get
data and choose from web, watch this and you thinking,
why am I going to web? Well, Power BI is really smart. So if I go here, what you wanna do is get rid
of everything after the X. So the question mark, web equals
one, just get rid of that. I've done a video on this that actually walks you through the step of connecting to an Excel file
that's stored on a OneDrive. Click, okay, and then
you'll need to authenticate. Choose organizational account. Go ahead and sign in with your credentials that allows you to access that Excel file. You will get authenticated
and just click connect. It's gonna do some work
and it's gonna show you all the information that's available. You can choose the table
or you can choose form one, I'm just gonna choose form one. There's my data and you can click load. You could just click transform, if you wanna transform the data up. I've already built a simple little model and imported some data and you can see that I have five surveys and there's the responses to the survey. If I go back to the web and
go to forms.microsoft.com, choose the form. I created a little survey and I'm gonna just click
preview to do a sample. So I have a watched Guy in a Cube videos? Of course I have. How long have I been watching them? From the beginning. What's my favorite topic? Data modeling. Click submit. I'm gonna close this and then I'll head back over here. It takes a few seconds
for the data to update but you click refresh a couple of times and you should see it. It should add another one. There you go. So now you can see I have six results where I started with five. You will publish this up to powerbi.com. And you guys are probably thinking, okay Patrick, can I
schedule this for refresh? Of course you can. This is what you need to do. Go out to the surveys, once you publish it, go find the dataset. And then my dataset is
Guy in a Cube surveys. Schedule refresh, you'll need
to enter your credentials and you choose OAuth and
whatever privacy level you want. Once you set that up, then you can just go to that dataset and click refresh, it's refreshing. And then I'll go over to my report and you can see that there's five results. If I refresh the page,
now there's six results. And just like that, I've connected to my Microsoft Forms data. And now I can schedule refresh. I don't have to download an
Excel file and, you know, maintain it and ensure
it's there every time I want to get the latest
and greatest survey data. What do you guys think? All right, are you working
with Microsoft Forms? Have you connected up to Power BI or have you done it a different way? I'd love to know. Let's continue the conversation where? In the comments below. If it's your first time visiting
the Guy in a Cube channel, hit that subscribe button. If you like my video,
give me a big thumbs up. As always from Adam and myself, thanks for watching. We'll see you in the next video.
