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

[@kiliev2007]

Your channel is one of the most underrated in the sphere.

[@juanpaolo21yt]

I did this technique using Vba. So when I saw this, I WATCHED THE WHOLE VIDEO!! This is faster N flexible. Thank you mr handsome.

[@juanpaolo21yt]

The only concern I have is, what if someone emailed me at 11am, and then sent an email again at 2pm with corrections. How does pquery treat it? Will that be treated as duplicates which means will mKe the table bigger?

[@MyAvn]

Thanks

[@paser2]

I love this, because it eliminates the need to Power Automate. Thanks for sharing this.

[@MGA19a]

This is like a mini-course on this topic - great stuff - thank you for posting this content !

[@paspuggie48]

I have used this solution before and my clients love it. Well done Chandeep for making a video about it 😊

[@cjimmer4877]

Amazing! Thank you for posting. I can't wait to try this out on PDFs

[@phoolkhan87]

Looking this kind of trick from last 11 months. Receiving more than 100 branches excel file having locked format and placing the same in my folder one by one and so on and so on... You can't imagine that what kind of relaxing I'm thinking. Goodly u r really 💕.

[@sankhlayashraj]

You were fantastic Changdeep. Thank you for building this for us. I had a use case where I do this process each month and I used to do it the traditional way of copy pasting the data. The timing is right and I feel I have have got this right. Thank you so much. You have been an integral part of my journey to success.

[@ocismorkayo4659]

Technical A-ha moment. Simple, quick, awesome. Many thanks Chandeep. A huge thumb up😊

[@keagankemp6275]

Aah this is beautiful, I had to use a flow but now I can do it in these easy steps thanks a mil

[@rangs1966]

Superb. Your teaching style is excellent .

[@DeepakKashyap-fr3ek]

Seriously, it is insane. .. Thank you Chandeep.

[@josecarlosconejo5724]

Really impressive!! I do have these cases at work and I keep on taking the time to save the files in one folder to which I connect Power Query. You got a new subscriber 😊

[@GoodlyChandeep]

Excellent! :)

[@patrickkinbonso1809]

​@@GoodlyChandeepbig fan. Quick question please. Does it mean then I can't delete that email from my mailbox or I risk PQ not extracting those emails anymore? Also would this make me a single point of failure as my colleagues have no access to my email? Finally, what happens if some attachments are of an excel doc as attachments and some CSV, do I need multiple queries to do the same extraction?

[@jibinvinod4337]

You have no idea, how usefull this information to me. you are a gem!

[@einoconsult5563]

Chandeep, at 7:21 when you use Excel.Workbook to transform to readable Excel tables, I have csv files, I have tried to replace Excel.Workbook by Csv.Documents but I may miss something here, any chance ? :)

[@einoconsult5563]

I figured it out, so that if it can help someone else = Table.TransformColumns(PrevStep,{{"AttachmentContent",Csv.Document}})

[@tersit89]

Perfect solution and very easy method, thanks a lot!

[@ursvenky6394]

I have been following you for power query solutions. Excellent solutions you provided. We are very that your on youtube to provide solutions or else i couldn't get solutions. I implemented things in powerquery which you suggested on KZbin and i got good appreciation in my org. It belongs to you as well. Thank you

[@rohitwaradpande2492]

Indeed very useful trick! Thanks for sharing

[@muneerrahiman9846]

Omg...this is suuper cool....thanks chandeep for such a great tip

[@vinhwizard]

Perfect solution, thank you. And we can save files automatically to a folder by Automate. Please make a more Video about this

[@ca.cs.subachandranmurugesa2560]

Wow.. this opens up whole new opportunities

[@unnikrishnansanthosh]

super cool time saver, thank you for sharing

[@eliezerhilario]

Excelente video. Esto me ahora un tiempo importante diariamente en mi trabajo. Muchas gracias

[@Archie167]

This was an awesome info, thanks for sharing!!

[@nonoobott8602]

This is very apt. I sure need this and you are so explicit. Thanks so much for sharing

[@DMatWork]

Very useful, thanks for the guide!

[@madhumatikoli1503]

Thank you...this is useful and to the point

[@kumarvg5383]

Thanks for this Video... I've already started to used it for my work 😊

[@emilmubarakshin49]

Absolutely awesome! Thank you!

[@natsarev]

That's cool! I have a daily parsing reports and I used to use macro that copied all of the attachements to a certain folder to wich PQ was connected. That didn't take a lot of time in my case but direct connection is definetly better :)

[@sass520]

Wht do you mean by " Direct Connection"? Did he use Direct Query here ? I didn't understand the type of connection used

[@natsarev]

@@sass520 I mean connection to inbox directly from PQ without having to save attachements to a folder first. It's not direct query.

[@Rice0987]

I saw kind manipulations with emails at Leila, but i didnt dig into this topic (i didnt see, what can i do with that). But now i know, where i'll try your precious technique, cause before this moment i would have to save that files, prepare them with vba and after update with pq and finally get the data i need.😌 I hope i'll be able to apply this lesson tomorrow at work. Great thanks, boss!🤗

[@arunkumarkesavan6077]

Harray Bro!!! Its really awesome. I recently tried using the same thing using Alteryx and Outlook macro and finally load the data into PowerBI. Now its super easy and skip the intermediate steps. As usual again its clear and useful !!! Waiting to see your subscriber to reach million !!!

[@NotBen101]

This should be required viewing for everyone interning rn. I see that there are tables for calendar invites. When I do audit work, I need to keep track of who I spoke to and when I spoke to them. This feature looks like it will help me keep track of this.

[@boochi_coming]

Thank you for the tutorial, it is amazing, how can we do on PDF's and CSV's ?

[@VIJAY-pd4jt]

Excellent video.🎉

[@ourlittlesmarties1188]

This is very useful! Thank you so much for sharing it. Love watching your videos and your explanation is perfect!

[@Ratnakumarwrites]

Wow... A great video and explanation aa always Chandeep...

[@adhamm5503]

Great stuff!! Thanks..what if I want to upload the last data received only rather then combining them?

[@ajkrishna001]

Very useful... Great info

[@sasda1234s]

Amazing, i will try this

[@raghavshanbhag9308]

great skill thank you

[@vashisht1]

It buy me some time to watch goodly videos on youtube😂...keep up the good work veera..

[@tezbriz]

Can you use Power Bi to do this too? I have to have outlook and excel open for this to work. I have it as part of a power automate flow. Is there a way to do it all in the background?

[@TheSuvankarKundu]

This was so awesome.

[@mohamedabdo-xg6tw]

Thank you for the trick, I was wondering if there is a way to auto refresh the query.

[@evetteprout9814]

Thank you very much, is it possible to also do the same and pull in the content from Outlook File Types (email attachments), instead of .xlsx files?

[@tezbriz]

Brilliant video, just what I was looking for. I have everything working fine. I used power query to clear the data down to just 3 columns and is updated regularly. How would I add a column that shows when the record on each row was last updated. ? When I start drilling down through the attachments I can see a date and time column of when the email arrived but haven’t been able to include it in the final query data I load.

[@IvanCortinas_ES]

Absolutely handy, Sir. Thank you very much for the impressive video.

[@SantoshBudakoti]

This is very informative and time saving... I have do this with Power BI

[@GoodlyChandeep]

Glad it was helpful Santosh :)

[@flyingHubby]

Thanks. So very nice video again. Of course I would need to make sure that the e-mails are kept in Exchange ... But since I cannot use Automate and Excel because I don#t have a business account, it is a nice workaround.

[@narendravishwakarma4646]

Is this same step. I can follow for archived/offline outlook file!! If not can you guide me to do the same with archived *.pst file

[@adin6429]

That's amazing. I wonder what if I want to update the source of the query with new attachment received from Outlook rather than combining them together. For example, instead of combining Jan and Feb .xlsx, I want to update feb.xlsx in place of Jan.xlsx.

[@pandeyshivam1]

In the DatetimeReceived column, just filter "is latest". All the best!

[@hazelcolon2827]

I’ve got so far until 4:54 and once I enter in the comparer as nullable function it says my table is empty? All help and guidance is appreciated

[@MissMicrosoft]

Chandeep you’re a Power Query rockstar 🤩

[@GoodlyChandeep]

I am cursed with bad data and defend myself using Power Query😂 Thank you @MissMicrosoft :)

[@MissMicrosoft]

Thank God for bad data! 💪

[@sunnygala7972]

Thanks Chandeep!

[@haintuvn]

Interesting video! How can I import data emails in shared mail box ( the company mail box which I and 3 other can access to) .

[@hz3jc9]

Great tip. Thank you so much But last step is not working for me, I am getting external table not in the expected format error, all my files are csv though, does this work for csv files ?

[@michaeldolan7507]

When you do the Table.TransformColumns what do you put for for PDF's?

[@KuldeepSingh-nq1vi]

Wow Great Video as usual keep uploading more such Videos.. ❤

[@MattiasGregersen]

Using PowerAutomate to handle this scenario for a shared mailbox today. If possible, could we skip that and use PQ directly? If so, I think that'd be a great next video. Thanks!

[@mayyyur_patil]

Hey Chandeep, I have a use case on which I would appreciate your insights. Use case is somewhat similar. I work for Digital Marketing agency and prepare reports from multiple clients, but in my case I get the Data on daily basis on irregular intervals. The Data that the client sends is the data for the ongoing month till date. The data has late attributions, for e.g. if today is 10th of the month and client has sent the data till 9th and on the next day lets consider 11th client will send the data till 10th but there's a catch the spends will change due to late attributions .i.e if for 5th the spends were 180rs when the data was sent on 10th then mostly ln 11th when client sends new data the spends for 5th will change from 180 to a new number. Current process I am following is: I have created a Master dump where I copy paste the crm data on daily basis. I overwrite the new data on old data and then do all of my transformation. Is there a way to automate this? Thanks in advance

[@atulbhandvalkar2510]

Thank you for such an amazing informative video. What if the we want power query to fetch email from any other folder? Plus if the data is coming through email on daily basis in MTD (month till date) and we need to replace the data on daily basis?

[@rainehardclarke7955]

I need this too

[@Chris-tj4es]

does it only work with Outlook? is it possible to import files from a Thunderbird email address?

[@CarollRobinson-r8u]

This is great and I am definitely going to utilize. I do have a question. What happens when the attachment email is deleted or archived?

[@rajkumarreddy-my4vd]

do we required separate mail id or we can use business mail id or our company mail id is fine

[@subhajitdutta8386]

Can we do the same and load data to power bi desktop

[@excel-jodallah]

Very very useful , Does this work with an email on the Hotmail server

[@HemanthKumar-lb4xt]

Awesome... Good learning from as always

[@GoodlyChandeep]

Thank you Hemanth!

[@aman_mashetty5185]

Awesome trick!! What if a person sent revised file example mar month he sent twice then both file will fetch then how to remove the old and consider into account the new one any idea how to tackle it

[@shubhamaggarwal6746]

This one is really powerful & time preserving idea. Thanks for sharing 🙌❤

[@GoodlyChandeep]

Thanks Subham :)

[@johnmanuelraj7555]

Very impressive

[@MagedSaad]

I keep getting an "ERROR" on the "AttachementContent" column at 7:18 after I press enter to Excel.Workbook(_, true) Attachements are CSV files When I press on any of the error I get DataFormat.Error: External table is not in the expected format. Details: 61d2c6e2-ff5c-4012-ae93-04982409fe45.XLS" can someone help me please?

[@GoodlyChandeep]

For CSV files use Csv.Document function

[@MagedSaad]

@@GoodlyChandeep Thankyou so much... yep working and saving me tons of time! :)

[@TheRathnakara]

Great Stuff.

[@prajapatirp]

This is insane Chandeep...

[@yam5400]

Is it possible to link this with for e.g. ChatGPT to get specific Information out of a pdf that is always different, like invoices and i want to get out the invoice date, amount, out of each of those?

[@dolphinloh7018]

Good day Goodly, can it be done for email tables info other than attachments?

[@warrenanderson6014]

This is great...does it work for emails sent to Gmail??

[@dc345601]

Thank you very much for this video! Extremely helpful. The documents that I’m trying to access are PDF and Docx. The documents unfortunately need to be reviewed and an over status assigned. How would you recommend going about this? Thanks again!

[@syednayyar]

Super Dooper amazing 80% of the times its the case ..!! its kinda Free Alteryx workings lolz

[@ahmed007Jaber]

excellent work, mate. wonder if there is a way to have a link for attachments so that I can scrape them and archive them. I have a use case to download 100+ files from emails

[@AnshulMarele-gbaa]

Can it also connect a hotmail ID?

[@Soydenoriadesopena]

It looks like you make it very easy, what can you recommend me to create a macro or something that will help me to solve a task I have to do. The idea is that when a specific email is sent with a specific file, this is automatically moved to a SharePoint location. Please anything can help me...

[@GoodlyChandeep]

This can be done very easily using Power Automate.

[@ananthalakshmiragi4446]

Can we refresh it automatically instead of us refreshing the file? Also I would like it if I can send out the consolidated data as a chart or as is via mail again.. please let me know if there is any other video covering this.

[@prashantsuthar7]

Thanks 👍. It works with my organization 365 office email ?

[@yoginathbadadale]

Can we map shared mailbox if yes any additional steps need to take please advise 🙏

[@DiwankarSingh]

can we do the same using Gmail and word file as attachment instead of outlook and excel????

[@seankovar6345]

At mark 7:36 looking at the data within the table, I noticed in my file I am not seeing all cell content in my last two columns which contains more information but not exceeding the character count limit. Is there a additional step in the custom formula I can add to ensure it pulls all cell content? Additionally, if I save the same file from which I am pulling from my email and use power query, this issue does not happen. Any help would be appreciated!

[@ashivns]

Can I login to a website and then navigate into same website to import data as table in power query

[@clementowusu2722]

Is there anyway I can use this step but make the new data replace the old one instead of combining the data please

[@georgekipan9246]

Thanks..Very interesting.How do I import a pdf and MS word from outlook?

[@celinaguerreropicon9685]

Would this work in power bi?

[@gauthamk1485]

Awesome Bro! This is very useful... how to get the data if it is moved to particular folder in Outlook?

[@dilipinamdarpatil6301]

This is amezing,🙏🏼

[@sasda1234s]

Is there any way we pick data from email body also?

[@josealvesferreira1683]

very good, thanks.

[@alexrosen8762]

Very very useful. Thank you for yet another informative tutorial 🙌

[@EllysaMaldia]

the function of table.transfercolumns is not working if the extension type used is in .xls format. the attachment content shows "error" when i followed the excel.workbook format. what format should i used then? thank you! :))

[@shreereddy6823]

Interesting. Does this work where an email account is hosted on Microsoft exchange server. Please clarify.

[@MarkvanValkenburg]

Excellent tutorial Chandeep. Cutting out the unreliable Outlook rules is a great step forward. Can you set this up for inbox sub folders?