Power Automate: Use Email Attachments to Update Power BI

[Music] foreign Jonathan Silva here back with another video taking a look at how we can use power automate in our lives to help us with any business decision that we need to make to to Really automate any of those manual repetitive processes today our video is centered around the integration of power automate SharePoint and power bi now what I'd like to do is just jump right into my use case here explain it by showing and then go build a power automate flow to help solve my problem so what I have here is a power bi report that I've created which is a pretty basic report here that I did it took me about 10 minutes put together just to Showcase some data here and what I have is just some dummy data around you know superheroes and some of the purchases they make from my fictitious company to help them in whatever tasks they have and the idea behind this report in power bi is to give me a little bit more information about what pro products they're purchasing most which superhero team is purchasing what and you'll get averages get totals get goals all that kind of good stuff the amazing things that power bi can offer us with these visuals now here's the thing that I most importantly thinking about right is every week I get more and more data okay about these sales or I'm selling to a new superhero team and all that kind of goes into it and what I like to do is automate a process that allows me to take email attachments that come in that has all the data stored in and then take that email attachment store it in like a in a nice location for me to use as my data source for this power bi file so all I need to do is with this power bi report is set up a scheduled refresh or do a manual refresh it once a week twice a week whatever it might be to be able to always have the most up-to-date data without me having to go in and reevaluate to move data around to really to continue to go through the process each time what I really want to do is automate everything here so all I need to do is Click one button or set up that schedule refresh and I will always have the most update data possible that is exactly what we want so let's go do it so really quick taking a look at my data here one of the things to notice about this data is I'm actually pointing to a SharePoint document Library my training site and what I've done is as I brought the data in from that document library that training site that I have is I'm using that site and you can see here's our superhero orders right here I'm using this site as the source and I'm combining all these files together into one table inside of power bi to be able to have all those columns all together now if you're interested in learning about that process and how to use the from folder option either on premises or from SharePoint within power bi I do have another video perfectly set up just for that that I would encourage you taking a look at there is a link inside of our little description here for this video just right there but for here we have our data all set up in here what I want to do is set up a flow that is triggered every time I get that email with that document and then take that document create a file here so then I can go straight to power bi and have it completely updated because I've already set up in power query that I want to combine all these files together so without further Ado let's get into Power automate and start setting up our flow to design the process so I'm going to jump into Power automate here get myself out of the way so you're not seeing me in the way and then let's go ahead and create a new flow so we're going to select the create option from our navigation pane in the left and we're going to choose an automated cloudflow in this case our trigger is going to be when a new email arrives in our inbox so for this one is we're going to put um save email attachments to SharePoint to update power bi now very long name in that case but it tells exactly what we want to do when a new email arrives is what we're looking for of course an Office 365 Outlook we're going to go ahead and create that and now when we're looking at our inbox here for when a new email arrives what we really want to make sure we're doing is setting it up so we're going to filter down to the specific option that we want right I don't want to add every attachment from every person that sends me something to that SharePoint site so I want to make sure I'm putting in a filter here so first things first we're definitely going to include attachments we are only always going to have only attachments as well and we'll do our subject filter here for the email super hero order okay so it's just the new superhero order not the greatest one here but we're just gonna run with it now we can hit new step and the next step that we want to do is go ahead and get the attachment from the email so we're going to go ahead and search get attachment and we're gonna go and do that from Office 365 Outlook okay so here it is get attachment from our email and now for our attachment here the message ID is going to be from the email that triggered off our flow so when a new medium low arise that is the message ID that we want to point to so let's go find that in our Dynamic content and then the attachment ID okay in this case is going to be our attachments okay so we're going to scroll down here and we're going to keep going let's make sure we get the right one okay attachments con attachments attachment ID there it is it always comes in twice and notice the moment we do that we are getting an apply to each Loop being applied here that's simply because we may have multiple attachments you can see that that's the plural s version of the word attachment so that means for each attachment that comes in this process will be done for every single one of them so even if I get three or four come in as an email that one time it'll create three or four files depending upon how many we get so now that we're getting that attachment okay the next action that I want to do is go ahead and create that file in SharePoint so you can go ahead and search for SharePoint and again we want to create the file right we want to create that file that's stored just like this so we're going to go ahead and find create file and it looks like it went right past it so there it is create file and now we just have to point to where we want to store this new file so I'm going to go ahead and point to my site my training site my folder path in this case Okay the folder that I want to point to so I can just go ahead and find the document Library here superhero order data the file name okay in this case the file name that I'm going to choose is the name of the file the of the attachment itself I'm just going to use the exact same one that came in okay the file content again we're going to choose the content of that file from the trigger whenever whatever came in from that that email we're going to use that and then that's it that's it that's the whole flow that's everything we want to take that email attachment place it into our document Library so I'm going to go ahead and hit save and the next step is let's see it work let's see it run and go through that whole process so I'm going to make sure that I can send myself an email and I have my email all ready to go over here so I'm going to go ahead and send myself an email I'm going to make sure I put in Super Hero Order okay make sure I don't have any extra signatures or attachments on here because that will come in as an attachment that we have we're going to go ahead and attach a file and I'm going to go ahead and drag it over from over here there it is this one's going to be all about the Avengers and their orders and we can go ahead and test it now obviously we could just run it and it should work as well but I always like to go ahead and do a test every time I first create a flow just to make sure everything's working just right to make sure I know it's running and all that good stuff okay there it is and now we can go ahead and hit send and now I should be getting an email at any moment here and it looks like I just got my email you can see it there It Is My Superhero order let's take a look at our flow if it continues to come through and our flow has run successfully so if we come over to our SharePoint site here and refresh there you see our newest one we have it in there Avengers orders just a few seconds ago so our very last step to test this out to make sure it works is we are going to refresh our power bi report and we will be able to see the newest data in here we should have another option here with our slicer to see our logo of the Avengers and that team should be placed in inside of our Matrix on the left so we're going to go ahead and hit refresh and there we have it our new piece of data has come in we have all of it there it is there's our Avengers you can see it's come up in my slicer I have it inside of every one of the visuals here it's been added to the data I have my measures all organized with it as well everything's ready to go right so we've been able to go through take that email attachment send it into SharePoint which is being used as the source of my power bi report being able to use that SharePoint document Library means I can combine all those files together and once I combine all those files together inside of power query for power bi I can have them all stored here in one single table all I did was update that table it's gone through every little step inside of power query to get to this point and I now have my newest updated version of the data right if I want to get this even further I can publish this out to the power bi service set up a scheduled Refresh on it refresh it four five six times a day in the middle of the night however I need so that no matter when I get those emails coming in it'll always just have that schedule set up and I can always make sure I have that up to date version of the data my power bi Port power bi report is ready to go and we're sitting pretty right no longer are we going through taking that email attachment and manually adding it in doing all those repetitive actions we are using power automate to do that for us well thanks for joining me again here as we take a look at how we can use power automate to benefit our lives that benefit our jobs make ourselves more productive and here we saw the integration of power automate power bi the Power Platform itself it's just a beautiful thing stay tuned for some future videos as we continue to look at how to integrate the Power Platform together and don't remember or don't forget to hit that subscribe and like button below make sure you're getting all the latest content from myself and all of us here at pragmatic Works bye now foreign [Music]

Channel: Pragmatic Works

Views: 13,087

Keywords: microsoft flow demo, microsoft flow examples, microsoft flow for beginners, power automate tutorial, power automate, power automate desktop, power automate for beginners, power automate examples, microsoft flow power bi refresh, microsoft flow tutorials, learn microsoft flow, power bi, power bi dataset, power bi dataset refresh, power bi sharepoint, power bi tutorial, power bi tutorial for beginners, microsoft flow, Microsoft sharepoint, Jonathon silva, pragmatic works

Id: C2zbv9ZfT-4

Length: 12min 32sec (752 seconds)

Published: Tue Oct 25 2022