picture if you will a world where you've got an Excel spreadsheet and maybe every month you bring in monthly sales information so here I have January through March and then come April somebody sends you a new file with a file attachment that has that month's sales figures wouldn't it be great if all we had to do was just receive the email and once we know we have that email we could go into Excel right click refresh and the new monthly information is automatically brought into our report we didn't have to open up the email we didn't have to save the attachment extract the data in fact you probably didn't even have to open up Outlook this is the objective of this video to show you how you can write a power query to reach right into your email account find the latest and greatest email attachment for your data and read that data directly out of the email attachment into your Excel file let's look at two different scenarios for doing this our first scenario will be when we only want to get the latest and greatest file so if we get a file every month or every week or every day whatever the scenario but we only want the last file so in this case I want to show sales by department so it's for whatever the last reporting interval is so I'd like to be able to go into my Excel file right click refresh and automatically get those numbers so the prior reporting periods data is completely replaced with the new reporting periods data the second scenario is when we want to retain historical information so we don't want to lose lose what we have we just want to add to it so like I said before we've got January February March I want to be able to right click refresh and now automatically have April so let's start with the first scenario I've included a link in the video description where you can download text files of the power query M code this way you can copy this code into a blank query and not have to recreate all these steps yourself now you will need to change a few things in the query for one you'll have to put your email address here and the pathing that I'm going to use an Outlook where I'm storing the emails you may have to change this to your own path other than that this code is fully baked so here's the M code for one of our scenarios and here's the mcode for another they're almost identical there's just some minor differences and this has to do with the way that we're retaining data I've taken the M code and translated it into somewhat English just so we can understand more easily what's happening there are going to be three phases to this project connecting to The Exchange Server to find the necessary emails extracting the data from the attachments of those emails and then cleaning up the data for our report the first two sections of the power query mcode will require very little alteration the first phase will only require you to put in your email address and a pointer to the folder where you're going to store these email messages the second phase the extraction of the data from the attachments is basically baked and everything from the data clean up down is completely optional it's just for me to be able to go in and build a chart but all the data cleanup steps will be based on what your data is and what you need it to be once it comes out of power query so for all intents and purposes the first two phases are the only ones that this video is really concerned with the first thing we need to do is create a folder inside of our email account to store all of the incoming emails with the file attachments the reason for this is if you let the email go directly to your inbox it's going to be a lot harder to distinguish emails with sale data attachment versus other emails with other attachments so I like to set up a separate subfolder and then have all of those incoming messages go into that subfolder that way when I scan that location the only files I should be seeing are ones that qual qualify for this report you can see here I have a folder called monthly report data and in here I have the email messages where I was sent the January data the February data and the March data now there could be other email in here like this one that doesn't have an attachment so I put this in here just so we can see at the metadata level how to identify a message that has an attachment versus one that does not so let's build the report that gets the latest month's sales which in this case is March in Excel we'll go up to data get data from online services and then Microsoft Exchange I'm using the Microsoft Exchange connector because my email address is an Office 365 address if your work email address is associated with an Office 365 account you'll still use this connector but if your email address is something like Gmail then this connector will not work for that I've not tested it but I've read that the obbc connector will work with Gmail accounts perhaps that can be the subject For an upcoming video so I'll go ahead and put my email address in here hit okay now I've skipped the part where I put my password in because that password was already cached but after putting in the email address you will be prompted to put in your password so from here we can see my email account and we can see the main folders for things like calendar mail contacts tasks Etc so I'm going to select mail and transform data so that will read the contents of my inbox and all subfolders I'm going to go ahead and minimize this panel and zoom in the first thing we'll do is filter the folder path just so we scan the folder that's going to hold these email emails with the needed attachments so for me that's going to be this monthly report data folder I'll hit okay and now we see entries for the four email messages that were in that folder so here's January February and March and then that junk email now we don't want the junk email we only want email messages that have attachments so if we scroll over to the attachments field we can see this column here called has attachments if it's set to true it has an attachment and if it's false it doesn't well I only want files that have attachments so I'm going to filter this for true and that gets rid of all the junk emails the next thing I want to do is make sure I only get the latest message so this is where the date time received column can come into play I'm going to go to my sort filter dropdown and do a descending sort so this ensures that the latest email received is the one with the latest sales information now we'll go up to keep rows keep top rows and I'll only keep the first row from the top scrolling over to the attachments column this column is what actually contains the file attachments and it's the only column I need so I'm going to right click and remove all the other columns that ends the first phase of connecting to the email account locating the folder of files and then only picking the most recent file if we click next to the word table this is the attachments metadata and we can see that the latest file was for March 2024 the files contents are in this attachment content column and that's what binary is referencing so when we expand this column we only want the attachment content column so I'll go ahead and deselect everything except attachment content deactivate the prefixing hit okay and now I've isolated the binary file this is what actually has the data now most people would go up and click this button called the combined files button but when you do that it ends up creating a whole series of helper queries that some people just aren't a fan of so I made a video that shows you how to pull the data out of the binary files without creating all those helper queries Link in the video description and in the upper corner so for more information on this technique go check that video out so what I'm going to do is go up to add column custom column and I'm going to use the CSV function called document it only has one argument and that is the source The Source being the column that holds the binaries I'll close parenthesis hit okay and I was able to extract that table clicking next to the word table we can peek into the file and we can see all the data I no longer need the binary so I'll delete that column and in the file attachments table I want all of this information so all four columns we'll go to the expand table button I'll tell it I want all four columns hit okay and now you've got your data everything after this is based on what you need to do to clean up the information to suit your needs doing things like going up to Home promoting the first row to a header Row setting your data types maybe filtering sorting it's all totally up to you everything that concerns this video stopped basically at the removed column step so everything after this is very Mission specific so we fast forwarded in time a little bit where I dumped the query output to this green table and I built a chart now the Moment of Truth here I am in Outlook I've received the latest and greatest file for the April sales and all I have to do is take that email message and put it in this monthly report folder now if you really want to get efficient in this process you should go up and create a rule a rule that identifies something like any email that comes in with the words monthly sales data in the subject line is automatically moved into that folder and marked as red this way you won't have to be burdened with the task of actually taking that message and dragging it and dropping it into that monthly report folder so now that I have the April data I'll go back to excel right click on the power query table output and choose refresh and now I've got new data and if you really wanted to turn this into a zero click solution we could go up to data queries and connections go to our query rightclick properties and we can even set this to automatically refresh as soon as the user opens the file you could even put it on a timer so maybe every 20 minutes every 15 minutes as long as you leave the file open it'll automatically refresh I'm going to hit cancel so now that we've seen how to create a report where it replaces the old data with new data now let's look at a report that retains the historical information and adds to that new information here we are back in Excel new workbook we'll go up to data get data from online services Microsoft Exchange I'll put in my email address hit okay we'll go to the mail section transform data start off by filtering the folder path the only folder we want is the monthly report data folder scroll over to the attachments we only want email messages where the has attach attachment column is true so we'll filter out the falses but this time I want to keep all of the files so I'm not going to sort by date and then only keep the first row I'm going to keep all of them so scrolling back over to the left you see we currently have January February and March let's go over to the column that actually holds the content we'll select that column remove all other columns peeking inside of one of these rows remember it's the attachment content field within this table that actually holds the attachment data so we'll EXP this column only select attachment content clear the table prefixing hit okay and then we're going to avoid creating all those helper queries by going up to add column custom column and we're going to use a CSV document function to point to that attachment content field close parenthesis hit okay and now if we peek into one of these files let me bring up the preview we can see this is now the April data here was the March data the February data the January data we don't need the binary files any longer so we'll delete that column we want every field within the table so I'll go to my expand table button select every column hit okay and now everything after this point is just standard power query promote your headers set your data types sort filter Group whatever it is you need to do for your data back in Outlook I've received the April data my Outlook rule is automatically placed it in my monthly report data subfolder we go back to excel where we have our monthly report right click on the power query output table refresh and now we've absorbed the April sales transactions so each month now we receive a new file the Outlook rule automatically puts it into the subfolder and power Creer will now scan that subfolder pull in the latest information update our report so how cool do you think this little bit of power query Wizardry is using power query to automate the small or more inconsequential tasks allows us to focus on the larger more important tasks let me know what you think in the comments and tell me if you've used this before if you think you could use it thank you so much for watching and remember at bcti the learning never stops