Business Application of POWER AUTOMATE | Connect MS Forms with Power BI

power bi power ultimate and microsoft forms let's see how we can use them together to build a beautiful survey solution now if you're new to this channel and you're looking for anything power bi related then make sure to hit that subscribe button now let's get started [Music] [Applause] so we are gonna build a survey using microsoft forms and then we're gonna have a look at different ways of how we can store our responses and be notified automatically when there is a new response and also to make sure that the report that we build using power bi automatically refreshes now as a first step we of course need to build the form so let's go to forms the dolphins.com and add a new one so over here let's call this one company survey and add a new question and it's going to be text answer and as a first question i'm going to ask what company do you work for now as a second question let's ask about the work environment how people rate it so how would you rate the work environment and then let's have a third one where we're gonna have a choice do you have a nice boss and then yes or no all right so that's it now let's see if that works so i'm gonna go over here to preview and type it in so the company that i work for data training that i o how would you rate the work environment five out of five do you have a nice boss yes and i'm gonna submit it all right so now if i go back then here in the responses you see we have now one response and it gives me already some insights now if i want to analyze this further i can go and click on open in excel and let's open it and here you see we have a response id start time then i started filling out the survey completion time and then over here my email name and then the three questions that were in the survey i could take this excel file import it into power bi and start analyzing it however the downside of this is that every time i want to analyze the new server results i have to go here download the axa file and replace the old excel file and this is not ideal what we can do instead is go to power automate and build the flow that when there's a new response this data set gets updated and i get notified by email so step two is to go to flow.microsoft.com and create a new flow so here i'm gonna click on create it's gonna be an automated flow which is then triggered by a certain event which is when somebody fills out the survey so let's open this one and here at the top you can already see when a new response is submitted that's going to be my trigger now let's also give it a nice name so over here is my survey flow and i'm going to pick the first one and let's click on create so you see now that form submission is going to be my trigger now i just have to select over here which form okay so i'm gonna have over here the company survey and then let's add a new step now here i have to choose the next action which is i want to retrieve all of these responses okay so here i'm going to look for form and then you see the first one is get response details let's take that one and also here i have to say which form so that's the company survey and then here once you click in the box for a response id we have dynamic content that we can add so you see how there is a response id now let's hover over it so the response id gets the response details and fetches the content of the form response okay so that's exactly what i want so the second step get response details will fetch all of the details for each response that we get now then we have to say okay where do we want to store it and for this we have a couple of different options maybe we want to store it in an excel file and a one drive maybe a sharepoint list or maybe in a push data set inside of power bi now let's go over these options one by one so first let's have a look at an excel file on a onedrive okay so i'm gonna go back to forms and i first gonna take that excel file that it generates this already gives me a structure for the table to which i want to add new rows for each response so let's take it as our basis and then save it to our onedrive and now we can go back to power automate and add a new step to a flow that is going to add a new role to that excel file okay so let's add a new step and here i'm gonna look for add a row and the first one that pops up and here add a row into a table excel online business that's the one that i need now of course you need to say where it is so pick one drive for business that's your location then i have one drive and then we need to select the file and there you go there's my companysurvey.xlsx and then over here the table inside of that file i didn't rename it nicely so it's still table one so now we need to map the answers to the questions or the different columns that we have in our table so first of all the id so for this we can choose the response id now here at the bottom we have the main questions like what company do you work for then we have how you would rate the environment so over here that's this one and we have a nice bus yes or no and then we have a few other boxes here for example the email address we can map that to the responders email and then over here for the completion time we can say the time of the submission and however for the start time there there's nothing okay so at the moment that's that's not possible to map so we leave that one empty and can take it out later from the from the exo file and then also here for the name so the name of the person who filled out the form there's nothing that we can map there now if you are interested in name department that kind of stuff then we can add a step in between and then here we can look for more user information let's see if we type in user profile there you go get user profile and then here as the user principal name or the user you can then use the respondent's email address okay now if you then go back and then you go to this name field you will see that in the dynamic content you have now more options like for example the birthday city all that kind of stuff and also the display name or the given name whatever you need let's add then also another step where we get notified by email when somebody adds a new response so here we can look for a send email and let's go here for version two so i want to send an email to myself okay so over here that's me and then as a subject i'm gonna say new response and then here in the body we can already put the answers so here we can use the dynamic content again so first of all we want to get the company name and then we want to have the rating and then we have an answer to whether it's a nice boss so now we can try it out so let's go back to a form and add a new answer so what company do you work for company b how would you rate it and we have a nice boss no i'm gonna submit it and let's go now to the exo file on a onedrive to see if a new row was added and there you go you see we have now our new response added to this file in the onedrive so the next step is to analyze our responses done inside of power bi so here i'm in a new workspace i'm going to click on new and i'm going to add a new data set that is then coming from onedrive so let's go to onedrive for business and then here we can select the company survey xlsx let's connect to it and here i want to go for import excel data into power bi so that i don't have to schedule any refreshes and let's then also add a new report on that data set so here create a report let's build a simple table visual that shows the responses to our survey okay so we have first of all what company do you work for and then how would you rate the work environment and do you have a nice boss yes so no now you see it returns one response and actually we had two responses already now few things that you need to check now first of all see if onedrive is already synced okay so that's fine and then second of all check if your data set for the company survey when it was last refreshed okay so here for me that was a few minutes ago but still before i added that second response okay now here for next refresh you see it's not applicable for files that are on your onedrive now why is that well if you would go here to schedule refresh and then onedrive refresh then you see that by default onedrive updates files hourly okay so the reason that the second response is not in there yet it's just it takes a little bit of time so there can be a bit of a delay so let's just check in a few minutes again and then it will be updated now what is important to know is that your data set will be updated continuously on an hourly basis or so and when you then open your report it will show you also the latest responses okay however if you leave your report open as it is now it will not automatically show those new responses unless you click on refresh okay now if it's really important for you that you can leave this report open and you see new responses coming in then we have two options either we go for a different data source than an excel file that's on onedrive which we're gonna do next or you go to your browser and you use some kind of add-in that keeps on refreshing this page okay so here i have one that you can just add to let's say chrome and you can say okay i want to refresh this every 60 seconds or so okay so that you see the new responses coming in so in the meantime my company survey dataset was refreshed automatically without having to do anything okay now if i go back to my report and i click in refresh you will see that the new response will come in there you go now let's have a look at an alternative now if it's really important for you that you have the latest responses popping up on your screen as they come in then we could go for a streaming data set so in our flow we have now when a response is submitted that stays as it is then we get the response details the user profile okay that's optional depending on what information you need but what we're going to change now is this tab over here we're not going to add a new row to an excel table so we're going to get rid of this one and replace it with a new action and this is going to be a power bi action where we are going to add a row but not to a power bi data set so you see add rows to a data set just be aware it's preview okay so i don't know if you really want to have this already in a live environment but let's go and try it out so here we can connect to our workspace and then we can select the data set however we don't have the data set yet so let's go to power bi service and add a new one so this is going to be a streaming data set and we're going to go for the api and click on next now let's call this one survey streaming data and here we can get the values from a stream so what do we need well we need three things we need the company as text we need the rating as a number and then we also want to know if we have a nice boss and that's going to be a text now here you see how it's stored it has a json file and then you have the choice if you want to keep historic data now if you don't turn on historic data analysis you cannot create a report you can only create a dashboard and it just stores the latest answers in temporary cache and also gets rid of them now by turning on historic data analysis you can store up to i believe 200 000 rows and that's kind of relevant over here because we do want to have historical responses okay so make sure that you have this one on and then click on create now back in power ultimate we can now choose our data set and you see now it is here survey streaming data can take a few seconds before it pops up okay and then here we have a table here you need to choose real-time data and then we see the three fields and here we can do the mapping again so we have the company now let's go to our dynamic content and search for company now what company do you work for then we can put in a rating so how would you rate the work environment and whether you have a nice boss and that's it okay so let's save the workflow and try it out again so i'm going to submit another response so what company do you work for no here we have company c it's perfect work environment and yes nice bus let's submit it and let's now go to power bi service to our workspace now here we have our service streaming data set so also here it's not necessary to put any refreshes so let's create a report now let's create a table again where we're going to show the company the rating and but we have a nice boss and you see that we have now company c rating five nice bot yes the previous two answers they're not showing up okay so i believe it's also not possible to get them back in so if you have old survey data then it's probably better to choose a different solution than this one now this is a streaming data set and you might expect that you don't have to click on refresh here now let's see if that is true so i'm gonna go and submit another response company d four out of five and no i don't have a nice boss i'm gonna go back and you see nothing happened and if i click on refresh only now it updates and this is confusing to a lot of people because you think oh i have a streaming data set it naturally flows in without you having to do anything however this is only the case for a dashboard for a report you still need to refresh that report page now let's take our survey and pin it to a dashboard so over here click on pin visual and i want to pin it to a new dashboard i'm going to call it survey dashboard let's spin it and let's open it up now we have our two responses let's add another one now what company do you work for well company e good work environment yes i have a nice boss and now i go back to my dashboard you see it just pops up after a few seconds without you having to click on refresh okay so let's do one more that is very common as a sharepoint list so i'm going to go back and over here delete that step where i add a new role to the power bi data set okay so let's delete it and let's go and add a new one add an action and this is going to be for sharepoint so let's type in sharepoint and i want to add a new item okay so let's go there and see what it gives us [Music] and here you see we have create item and here we first need to say what is your sharepoint address so let's type it in then we're going to choose the list name but for this to be there we first have to create one so let's go to sharepoint and then add some new content which is going to be list and this is going to be my survey responses list here we can add our columns so let's go there and for the first one we're gonna have some text and this is gonna be their company everything we can leave as it is and over here i'm gonna add another one for the rating and then another one where we're gonna have whether it's a nice boss and then over here for the title let's rename it to a response id so now that we have our list we can go back to power ultimate and then choose it from the list sorry responses so after you pick the list then you see we have the title company rating nice boss so for the title we can map it to the response id so let's look for that and then we have the company then we have the rating and we have whether it's a nice bus so let's save it and try it out again okay so i'm gonna submit another response this is gonna be company f okay bad work environment not a nice boss and now let's go to sharepoint let's see if there let's refresh the page and see there it is okay so now i can connect to the sharepoint list inside of power bi and start analyzing so let's go to get data this is going to be a sharepoint list and put in the site url and then look for our server responses so survey responses over there okay so now we can create a table again so here we're gonna have a table now you see when you have a sharepoint list you have all of these columns that you actually don't need and you can just remove them using power query okay but for now i just leave them in and i'm gonna choose company then i'm gonna choose rating and whether we have a nice boss okay so once you have this publish it to the workspace and let's see if it automatically gets refreshed and let's open it up in power bi service and there it is so let's see if it automatically refreshes let's add another answer so company g okay two out of five nice buzz though and there you go i'm gonna go and jump back to power bi and you see it's not there now i refresh the page and see even after i did the refresh it's still not there and then you might think yeah but with one drive you just have to have to wait a few minutes and then it pops up but here you can keep on waiting because it will not pop up okay so what you need to do is you need to schedule a refresh this is option one or you go back again to power ultimate and add another action that in the end it refreshes this uh data set okay so let's go to our data set so every response is sharepoints and then over here i can schedule a refresh now i probably have to go first and put in my data source credentials just double check that and then you can go back to schedule refresh turn it on and then over here how many refreshes you want if it doesn't work if you need to be notified and then just click on apply so if i go over here to my service responses sharepoint i would refresh it now manually and then go to the report let's refresh this one as well you see it updates okay but i have to manually trigger it or schedule a refresh now you see for each one of these three approaches the refresh is a little bit different okay so with the sharepoint solution you need to schedule refresh otherwise the data that doesn't update automatically and that is a big difference to one drive okay so if we go for that solution you don't have to do a schedule any refresh it just automatically updates okay um then with the streaming data set you also don't have to do anything but the big difference there is that if you really need this up-to-date dashboard that always shows you the latest responses coming in on one screen then that's probably the way to go or you go for the onedrive solution and then keep on refreshing the browser page isn't it beautiful how all of these three different services nicely work together i think it is now if you have any other interesting ideas of how we can set this up using power ultimate microsoft forms and power bi to visualize then share it with us in the comment section below and if you want to stay up to date on everything power bi related then make sure that you subscribe and i hope to see you in the next video

Channel: How to Power BI

Views: 6,980

Keywords: power bi automate, automate power bi, power flow, power flow power bi, ms forms power bi, connect ms forms to power bi, power bi refresh, power automate ms forms, power bi videos, microsoft automate, microsoft forms power automate, power automate tutorial, what is microsoft flow, microsoft flow, power automate excel, how to automate in power bi, power bi, power bi tutorials for beginners, power bi desktop, power automate, power automate example

Id: ie7HB6yv8qM

Length: 23min 0sec (1380 seconds)

Published: Sat Oct 31 2020

Reddit Comments

[u/ultrafunkmiester]

Simplest one. A form from 0365 excel. A single cell question of weight. Had a colleague wanting to lose weight so he opens the url enters the weight. This date/time stamps it into an excel onedrive file. Connect power bi and use that data to combine with loss to date, weekly weight loss etc combi ed with a what if parameter for target weight it works out the date he will hit his target based on current weight loss rate per week. Also a lard-ometer infographic that shows weight loss in 250 gram blocks of lard. All from a single cell form. Have done much more complex and boring ones but I still love that application because it illustrates how to create depth from a single field and a time series.