Forms - Power Automate - SharePoint and then Showing the Most Current Submission in Power BI

hi my name is jeff rhodes and welcome to another video about office 365. uh this one's similar to an example that i i did in my book but it's one that just came up this week so um we're going to really talk about a form that you then uses uh power automate to load the information to sharepoint and i've covered that before but the main innovation here is is we want to just show the most current version of whatever they filled out so if they change their answer we want to store all of them which is one reason the microsoft form solution is a good one as opposed to power apps but we want to go ahead and store the most recent one so that's got a little bit of gyration inside of power bi so um let's get started so a little quick about the book you can get anywhere you order books including on amazon here so check it out so let's start with form so this is going to be a really simple form so i'll just call it information request and in the real one that i did for this is i recorded the name um but this one but i'm not going to do it here because in my this my personal office 365 so the only person i could do is uh myself so that doesn't do a whole lot so i'm gonna i'll put the email address in there instead and i could set anybody can respond but either one doesn't really matter i'll just do that and then and actually one that was important is we wanted to not check this one response per person so obviously the minute we do it anonymous that doesn't come up but if in the real one we want to let somebody come back later and change their answer so i'll just change it to there and uh so let's just add something in so first one i'll just say please enter your email address and again in the real example of this we didn't do that because we could record who it was after they authenticated in and then we're going to say please answer this question i'm not going to necessarily tell you what the question was but i'll just call it option one we can just do option one option two option three option four that's fine make that required as well so that's the entire form okay and we can preview if you want but i'll wait till we get there so let's talk real quick why do i want to use power automate and put this into sharepoint so a couple reasons so first one is if you share the form with anybody so you can come over here and share it and then share a link to collaborate which gets into that to this spot but if you do that then they can come in and actually delete the responses you know and come over here and delete all responses when they look at the results and then you've basically lost all that you know data and you know you don't feel that comfortable with important data that we don't necessarily have a backup and somebody can just go in and you know intentionally or not delete that the second one is in the in the environment that we're looking at we have some users who may be on a detail or something like that where they don't have access to a uh to a form or a computer that they can fill it out so somebody's got to fill it out for them but in this solution it ah it only prompts the office 365 account but when we get to sharepoint as long as we lock that thing down to just specific people who have rights they could add a new record for that person if they if they got like you know an email or something with the person's info or some sort of phone call all right so let's go over to sharepoint so i've got this sample site we'll go to site contents and we'll add custom list and i'm just going to do a blank list and i'm just going to call it uh information responses and i'm not going to show it in the site navigation and it's really actually important that we lock this down quite a bit so i'm going to come in and go to list settings so we get a little that you know we can get it exactly where we want so the first thing we'll do on this permissions is that we'll stop inheriting permissions and then we're going to give everyone the rights and it could be contribute or edit turns out that contributes a little bit less of a of writes that can't mess with the columns and so forth so we'll share that now a real important point now and that we're basically using sharepoint as a data source but it's possible somebody could guess the the site or somehow find it and so uh it's real important that we go in and because it didn't even put it under my recent so let's go back to site contents for a minute and we'll go to settings again and we go into advanced and we change the access again somebody who's anonymous they wouldn't be able to get to this site anyway but uh you know in the way that i used it in the in the real world is you know they could get to this site but we want to make sure that they can only see their own data if they did find this site in the first place so that's real important so we'll say okay there all right so let's look in this case i don't want title but i'm just going to call this email instead and it doesn't really matter we're using a flow to fill this out anyway so whether i make it required doesn't really matter and i'm going to make a another column now notice there's a created column already but i like to make it um my own you know because it will bring a completed date so i'll bring that over i'm going to just bring that as text i actually like to just do it straight as text so we make sure we don't any problems with the flow and they want to get to power bi i'll format it so i'll do that and then of course i need the what they answered and i forget now what i called it i wasn't paying attention i just said and please answer this question so i'll just say question i'll just say answer here now you got to really be careful if you have like in our real world when we had a whole lot of text that kind of came through so i made it multiple lines but in this case since it said option one option two i can do single line as long as i'm sure it's not more than 255 characters because then you see that right there otherwise the flow will fail so be really careful there all right so that should be everything we need and again on our design we're going to do is just have this get added from power automate so let's jump over to there go to my flows create a new flow from automated and i'll just say information [Music] response to sharepoint and you see it kind of already notice when a new response is submitted that's what we're going to do and i'm going to create and i pick a form information request and then i come to form as well because it turns out i have to then do get the response details so i'll do the same thing and i only have one choice here the response id so that's pretty straightforward you've seen that on other videos and then what i want to do is come in put in sharepoint and i'm going to create an item and i then have to tell it where it go where it goes and i think it's this plot canyon one we'll see no it wasn't so since i don't know for sure what oh is the 542 one remember we saw that on the list actually i'll just select it here what's that one got two of them with the same name there we go information responses and then what's cool is it will show me the thing from from the form so i can just say okay please into your there please answer this question oh i forget oh he's in here oh yeah that's right and then the responders email i thought that i turned that off that i guess it's it's showing that even though it oh this'll be blank because it's anonymous so we can't really use that but if we could that's the one we would pick and we wouldn't have to put the responsive email but we should get the submission time in there and that's it so we save all right let's go try our form and let's see if it works so we'll come over here and preview and i'll just enter my [Music] comm and i'm going to say option one and i'll eventually change this to another choice so let's do another one and i'll just say a b com and i'll do an option two and i'll submit and i'll do another one bfc.com and i'll do option three all right let's go over to flow come over here and see if it's got it looks like it succeeded so that's good let's go over to oops go over to our sharepoint site you can see okay that's working i got those i got my dates got my answers so that's good and let me go answer that one more time i'll go back to my original email and let's say that i change my answer from option one to option four so i go back over here and reload see i'm in here twice and this is a slightly different later time so this that option four is the one that i'm going to want all right looking good let's go over to power bi i've got it open already let's do sharepoint i'll do a sharepoint on my list i like to do data i'll copy that so that i get exactly what i want and i don't need these advanced options i'll get all the columns so that's fine and i want this information responses you can see we're getting our data so that's good let's move that up and unload it so it shouldn't take long and first what i'll do is set this up without which is showing all the data and then we'll come in so i'll transform the data to see if i do a couple of things so the main thing notice it says abc here so i want that to change the type and make it a date time and as long as that one does the rest of this yeah i don't really need to do anything else that all looks good on email so let me close and apply and we'll do just a really quick visualization so there's really not much that we can do but i usually like to put a card in here i'm gonna put it in the upper right and i'll say i'll grab the id in there click account i'm sorry you want that i'm going to come in and i'll just rename and i'll usually call this number of responses put that clipboard in case i need to use it again so we'll do that and i may put a slicer on for the who did what um so let me find my where my slicers go i'm blind for the moment oh there it is just put that over here as well and we'll add on the answer and since i only have four i'll i won't change it to a drop down i'll just do like that and then i'll uh i'll put a pie chart as well again put the so we'll put the answer in there and the id that's the count paste that in there nice and big and then we'll probably let's do one more of those and we'll show the email address just in case we want to [Music] um yeah actually i don't want to do that and the real version of this i have like you know the organization so i could have done that as well yeah it looks good let's put a table across the bottom and we'll put the email and the answer maybe but the completion date as well oh and i always take that one and break it out so i'll put that as get rid of the date hierarchy and sometimes i'll get rid of the time in this case i'll leave it on there just so we can show notice that it's given both of the uh of my answers so um and i may rename this and call it status all right so if we just wanted to show just wanted to show all the data we'd be good to go but we only want to show the most current one of the the person so we got two of these guys which we don't like okay so we need to just get the most recent one so let's go back to trans uh transfer data and actually i really should let me go ahead and save this and i'll just call it uh make a new folder and we'll just call it information show you good practices by saving before we get too far into that let's go and transform the data again and let's go over to transform and we want the key is we want to group by so this is kind of a trick i found online so you've probably seen it as well so we're going to go advanced trans transfer and we're gonna call this one all data and we're just gonna get all rows and it doesn't have one and then we're going to add an aggregation and i'm going to call it max date and we're going to get the max of the completed date so this is the key is it's going to give us a new column that has whatever the latest date that we have so let's look at that and now we need to split out just get all our data back so we can come back here and go ahead and we don't really need all the stuff but we just need because we already have the email so we want to just let's uncheck that and we don't need the original column names but we'll get the completed date the answer and the id back we need for sure we could get all this other stuff if we wanted to there we go so we get our thing so we have a completed date and we have a max date so we're making progress but we're not quite there yet so we go in and add a coup oops i need to get off there there we go add a conditional column and i'll just say is latest entry or i'll say his latest response there's a couple ways to do this but you can kind of see where we're going so if i'll say if the max date equals i need to do the column if it equals the completed date then i enter true else i enter false so you can see okay our later one our first one am is false but the second one 12 27 oh i'm looking at the max date sorry here's the completed date the 12 26 one is not the max date so it's false the 12 27 one is the max date and it's true and then since it still says one two three i'll come over here and change type and make that a true false just so i recognize it so uh hopefully that makes sense if not definitely watch the video again or you can check out some things so we're going to apply changes nothing's really going to change yet because we haven't used it yet but now what we can do is all filters on this page i can get his latest response and only take the ones that's true and then see what's happened so i i'd move this i only got my last one and i eliminated the entry once or the earlier one so that if somebody came in and and changed their answer we could go back and look but our our dashboard would only have their most current one so that's it hope that you enjoyed this and look forward to seeing you in a later video down the road

Channel: Jeff Rhodes

Views: 920

Keywords: Filtering, Microsoft Forms, Most Recent Submission, Power Automate, Power BI, SharePoint

Id: 6S5lw1cu9H4

Length: 21min 3sec (1263 seconds)

Published: Sun Aug 15 2021