Auto-Import Data from Email Attachments to Excel using Power Query

let's say you are coordinating a marketing campaign along with an external agency that sends you the data as an Excel attachment every single month now obviously you would want that data to be included into your marketing dashboard so why don't we set up an Automation in power query that takes a look at your inbox or your outlook see if you have received an email and if the email has an attachment or not and then goes ahead and copies the data of their attachment into an Excel file and you can use that data to do anything I hope that is going to buy you some time maybe to watch Netflix or do whatever you like well no further Ado let's go [Music] okay people here I have my inbox open and you can see that I have at the moment received two emails right here uh one is for Jan data the other one is for the Fab data let's just take a look at what emails are these so typically the email that we tend to get has these keywords media spends in the month of Jan 2023 and the Excel file contains the information as to how much spends have you done on the media and that is the data that I'd like to collect from this Excel file and keep combining that in a single Excel file so Jan plus Feb plus March so on and so forth take a look at the Jan file if I just maybe click on the Gen file it has three simple mock columns at the moment we have the date column we have the media column and the cost as to how much have we spent now if I take a look at the next file which is the month of Feb it is also going to have the same columns however the number of rows can be different but still the very same columns date the media and the cost and the next next time I get the month of March file I somehow want to take all of these files from my inbox collate them append them and have them placed in a single Excel file to do all of that work let's just fire up power query and connect power query with our Outlook inbox all right I have opened up a blank power query in a blank Excel file and that's where I will start to connect with my Outlook what's the command that connects to the output you can use a very simple function called exchange dot contents and in exchange dot contents you have to provide your Microsoft email ID to connect to that now I'm just going to mention my email ID right here and that is going to connect to my inbox as soon as you provide your email ID in the quotation mark it is obviously going to ask you to authenticate your email ID provide the username and the password and as soon as you commit to that you're going to see that you get all the contents of your email you get the mail the calendar the people the tasks and the meeting request in this scenario however I'm interested in taking a look at all the mails that I have received so let me just open up this particular table in fact let me load this table so I'll click on the table to load that table which belongs to mail now this has loaded all the emails that you have received but I want to have this table filtered down to the emails that is sent by a certain user having a certain email address so on and so forth so you can see that here if I take a look at this particular column which is the sender column in the center column we have a record and the record contains two Fields the first field is the name of the sender and the second field is the address or the email client now this is the email client that I'm trying to gather the email addresses from so what I'm going to do is I'm going to open up this particular record I'll open up both of them the name and the address click on OK and I will go ahead and filter that the address should be equal to this email address from which I am trying to get to the email so I'm going to apply a simple filter here and just maybe pick up that email address click on OK and that is the email address now this email address should not be picked up in case you have a generic email from that email address it should be picked up only if you have email attachments from that address so let's just go ahead and take a look at a few columns on the right here and you're going to see that we do have a column called has attachments if you have an attachment only then filter that so I'm going to click on right here and I'm gonna say that hey please just give me the email only if the email has attachments click on OK and it's only going to pick up email with attachments the next filter that I'd like to apply is a filter on a typical subject line that I'm trying to trace so here is a subject line media spends for Feb media spends for Jan media spends is the common word that I see and I'm gonna instruct the person who's sending me the email to always include these words in the email subject line so I'm going to go right here and I'm going to say that here in the subject the text filters should contain the two words media spam as soon as I click on OK this is going to just work fine but I want to make this case agnostic that means even if the person doesn't really adhere to the case sensitivity which we have as a problem or as a feature in power query I'm going to go ahead in my text Dot contains function and I'm going to say something like that here the comparer should be ordinal ignore case and that is going to make sure that the case sensitivity of this word is ignored once you're trying to apply the filter so let's just keep only the few columns that we need in this particular table so I'll keep the subject column I'll keep the date on which I have received so date and time received and then I am also going to keep the attachments column which contains the Excel files or anything else right click remove all the columns and these are the three columns that I have now let's just go ahead and expand the attachments and take a look at what all attachments have we received so I'm just going to click on the expand button right here it gives me a lot of these columns and I'm going to only use the name of the attachment the extension of the attachment and the attachment content itself click on OK and those three columns are expanded now from here on I can just maybe go ahead and apply a simple filter then in case the attachment is anything other than an Excel file I want to discard it so I can just go here in the extension column click right here text filters should be equal to Dot xlsx and then click on OK at the moment since we just have Excel attachments it's going to be okay and then I'm just going to go ahead head and work with the boundaries now these are the Excel files that I have received I need to open up these files and start to work with the data within these files all right to be able to work with these binary files I'm going to create a very simple step right here and I'm going to use the function called table dot transform columns and the table dot transform columns function is going to give me the ability to convert these binary files into an Excel workbook readable format so I'm going to say table dot transform columns and let's just go back to the previous step to take a look at this the name of the column is called attachment content I'm just going to copy that and come right here and I'm going to say that the column that I'm trying to work with which contains the files is nothing but attachment content and every single binary of that file needs to be converted into an Excel readable format and the function to do that is nothing but Excel dot workbook now when I'm converting the Excel binary into an Excel workbook readable format I might as well promote the headers right here so I'm just going to go ahead and say something like a underscore true the underscore is the binary itself which is every single item in the row and the true means promote the headers so that we don't really have to do promote headers later just saves me like one additional step I'm just going to go right here close the brackets and press enter and this is going to give me a table format in the table I have the sheets and within the sheets I have the data so let's just go ahead and expand the attachment content column I'm just going to maybe expand the name and the data column and maybe also the kind column right here click on OK and we get the data now if I peek into the data this is the data for the month of Jan sorry the month of Feb and this is the data for the month of Jan all right from here on it's ridiculously simple all that I would want to do is keep the columns that I need remove the columns that I don't need and expand the column that I have the data in so subject column is the column that I need uh file name column extension column is something that I don't need so let's just go to choose columns and pick up the columns that we need subject is needed time and date received is needed name of the file yes I can keep that extension is something that I don't need name of the sheet I don't need that the data is something that I need and the kind is something that I don't need click on OK and that's the the data that I have received I can simply click on this particular expand button since I know that the Excel files is always going to have the same three columns date media cost I can click on OK and the columns get expanded and we are good to go now let's just load the data into Excel all right the data has been loaded into Excel let's just test it out does it work or not work in case a new email is fired for the month of March all right what I'm going to do is right here I'm on my Gmail account and from here I have attached the March Excel file and I'm sending to this particular user on which I have built the query I'm going to send it off from here all right I'm back at my inbox on the Outlook and you can see that we have received this March Excel data let's just go back to Power query and try to refresh our query I'm going to come right here right click and then I will say a refresh at the moment we had 25 number of rows it just adds 11 more rows to the data and we now have the data for March as well this is pretty damn sexy all right let me know how did you find this one obviously this solution can be applied to PDFs csvs or Excel files pretty much anything that can be ingested by power query cleaned up by power query and then you can use the data in further data analysis or any of your work let me know if you find any use case of this in your kind of work and if you have any questions around this please feel free to drop in a comment and I would be glad to reply in the end I'd like to give you a big shout out about my tax and my power query training courses in case you are a beginner in power bi you're trying to build up really solid fundamentals of learning Dax good data modeling and power query I would highly recommend that you take a look at my courses it is going to be super awesome thanks so much for sticking all around and I would catch you guys in the next one cheers and bye now [Music]

Channel: Goodly

Views: 45,454

Keywords: Goodly, Excel data, email attachments, merge data, automation, time-saving solution, import data, email automation, productivity, Microsoft Outlook, data integration, Excel automation, learn power query, godlly

Id: QCZtkojwAb8

Length: 10min 13sec (613 seconds)

Published: Sun Jul 16 2023