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

Рет қаралды 47,453

Күн бұрын

Check out our newly launched M Language course ↗️ - goodly.co.in/learn-m-powerquery/
Are you tired of manually importing Excel data from email attachments every month? In this video tutorial, I will show you how to set up an automation using Power Query to automatically import Excel data from email attachments and merge them into a single Excel file.
This time-saving solution will allow you to effortlessly extract the data from the email attachment and use it for further data analysis. Watch the video to learn the step-by-step process and start saving time today!
#ExcelDataImport #EmailAttachments #automation #powerquery
===== ONLINE COURSES =====
✔️ Mastering DAX in Power BI -
goodly.co.in/learn-dax-powerbi/
✔️ Power Query Course-
goodly.co.in/learn-power-query/
✔️ Master Excel Step by Step-
goodly.co.in/learn-excel/
✔️ Business Intelligence Dashboards-
goodly.co.in/learn-excel-dash...
===== LINKS 🔗 =====
Blog 📰 - www.goodly.co.in/blog/
Corporate Training 👨‍🏫 - www.goodly.co.in/training/
Need my help on a Project 💻- www.goodly.co.in/consulting/
Download File ⬇️ - goodly.co.in/how-to-import-ex...
===== CONTACT 🌐 =====
Twitter - / chandeep2786
LinkedIn - / chandeepchhabra
Email - goodly.wordpress@gmail.com
===== WHO AM I? =====
A lot of people think that my name is Goodly, it's NOT ;)
My name is Chandeep. Goodly is my full-time venture where I share what I learn about Excel and Power BI.
Please browse around, you'd find a ton of interesting videos that I have created :) Cheers!

Пікірлер: 180

@ourlittlesmarties1188 Жыл бұрын

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

@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

@MGA19a 4 ай бұрын

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

@kiliev2007 Жыл бұрын

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

@keagankemp6275 Жыл бұрын

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

@paser2 15 күн бұрын

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

@jibinvinod4337 11 ай бұрын

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

@ocismorkayo4659 11 ай бұрын

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

@alexrosen8762 Жыл бұрын

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

@vinhwizard 25 күн бұрын

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

@nonoobott8602 Жыл бұрын

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

@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 !!!

@Archie167 11 ай бұрын

This was an awesome info, thanks for sharing!!

@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 💕.

@ursvenky6394 7 ай бұрын

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 KZfaq and i got good appreciation in my org. It belongs to you as well. Thank you

@DeepakKashyap-fr3ek 6 ай бұрын

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

@emilmubarakshin49 Жыл бұрын

Absolutely awesome! Thank you!

@Lalithchary 5 ай бұрын

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

@IvanCortinas_ES 11 ай бұрын

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

@DMatWork 10 ай бұрын

Very useful, thanks for the guide!

@tersit89 5 ай бұрын

Perfect solution and very easy method, thanks a lot!

@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!🤗

@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 5 ай бұрын

@@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?

@unnikrishnansanthosh Жыл бұрын

super cool time saver, thank you for sharing

@rangs1966 Жыл бұрын

Superb. Your teaching style is excellent .

@kumarvg5383 Жыл бұрын

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

@ca.cs.subachandranmurugesa2560 Жыл бұрын

Wow.. this opens up whole new opportunities

@madhumatikoli1503 2 ай бұрын

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

@rohitwaradpande2492 Жыл бұрын

Indeed very useful trick! Thanks for sharing

@vashisht1 Жыл бұрын

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

@shubhamaggarwal6746 Жыл бұрын

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

@GoodlyChandeep Жыл бұрын

Thanks Subham :)

@KuldeepSingh-nq1vi Жыл бұрын

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

@muneerrahiman9846 Жыл бұрын

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

@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.

@TheSuvankarKundu Ай бұрын

This was so awesome.

@Ratnakumarwrites Жыл бұрын

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

@ajkrishna001 Жыл бұрын

Very useful... Great info

@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.

@prajapatirp Жыл бұрын

This is insane Chandeep...

@VIJAY-pd4jt Жыл бұрын

Excellent video.🎉

@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.

@TheRathnakara 3 ай бұрын

Great Stuff.

@SantoshBudakoti Жыл бұрын

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

@GoodlyChandeep Жыл бұрын

Glad it was helpful Santosh :)

@BertandRussell Жыл бұрын

Incredible!!!

@sunnygala7972 Жыл бұрын

Thanks Chandeep!

@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?

@josealvesferreira1683 10 ай бұрын

very good, thanks.

@navinsiriwardena6635 2 ай бұрын

Thank you :)

@raghavshanbhag9308 4 ай бұрын

great skill thank you

@sasda1234s Жыл бұрын

Amazing, i will try this

@johnmanuelraj7555 7 ай бұрын

Very impressive

@HemanthKumar-lb4xt Жыл бұрын

Awesome... Good learning from as always

@GoodlyChandeep Жыл бұрын

Thank you Hemanth!

@yoginathbadadale Жыл бұрын

This is awsome

@jorstube Жыл бұрын

Maravilloso

@MyAvn 2 ай бұрын

Thanks

@dilipinamdarpatil6301 Жыл бұрын

This is amezing,🙏🏼

@WWIESE1000 7 ай бұрын

Another great solution provided by you! I really enjoy watching your videos, which are really helping me finding the way thru Power Query. Is this a possible way to import XML files as e-mail attachments?

@syednayyar 2 ай бұрын

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

@evetteprout9814 6 ай бұрын

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?

@dc345601 11 ай бұрын

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!

@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!

@tezbriz 7 ай бұрын

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.

@Ody2004 Жыл бұрын

Great video! I was just using Power Automate to load email attachment to Power Query last week. Btw any chance to load a table from email body to Power Query?

@gauthamk1485 11 ай бұрын

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

@dmclean3324 Жыл бұрын

outstanding

@ramruttunaubeeluck9235 Жыл бұрын

Awesome

@ahmed007Jaber 8 ай бұрын

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

@MarkvanValkenburg 4 ай бұрын

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

@dbwkg6885 Жыл бұрын

Awesome, as always, very useful....but now, a new challenge what about Teams and files embedded ? How could we process this kind of issue? Thank you so much Chandeep

@dolphinloh7018 11 ай бұрын

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

@user-ek2rk4tv5i 5 ай бұрын

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 ?

@excel-jodallah Жыл бұрын

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

@mirrrvelll5164 Жыл бұрын

Great! Can it be applicable if I am receiving .zip file (with .csv inside) and then to extract it?

@tezbriz 2 ай бұрын

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?

@jithuraphael1993 Жыл бұрын

Thank u for an informative video. And one request instead of appending the data can u also give a solution how to save the data into different sheets in excel.

@prashantsuthar7 Жыл бұрын

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

@roywilson9580 Жыл бұрын

Thanks for another interesting and informative video! I can that this could be a good tool to have in your arsenal but think I would prefer to save the attachments to a directory that way there is less chance of losing the data if an email is deleted or archived and the query will continue to work if the responsibilty for tracking these data is passed on to someone else within the organisation. Of course if you want to make yourself indespensible ensuring the query is reading from your and only your inbox would be a good way to ensure continued employment and leave the client screwed if he decides to replace you with a minimum wage kid LOL!

@SparkVox 6 ай бұрын

Great work brother, what should we do in case of multiple attachments

@ExcelAZ Жыл бұрын

💥

@SAIKISH2005 Жыл бұрын

Fantastic ji. How about if the same pattern of pdf files received in emails from specific person like bank statements? Can we do the same steps?

@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!

@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 4 ай бұрын

I need this too

@J1N-7 10 ай бұрын

Hi, what's alternative for CSV? The table.tranformcolums for each excel.workbook produces error column instead of excel, any help would be appreciated! I'm working with CSV rather than xlsx.

@georgekipan9246 Жыл бұрын

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

@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! 💪

@michaeldolan7507 Жыл бұрын

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

@TheJaime10000 Жыл бұрын

This video is so informative! Can you explain how I could apply this to pdf files?

@evangelicana5083 2 ай бұрын

this is very useful to me. but I need to watch it over and over again to remember the process..😂

@abdulsaidmkamara7305 Жыл бұрын

WOW

@rajkumarreddy-my4vd Ай бұрын

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

@warrenanderson6014 Жыл бұрын

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

@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

@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

@Chris-tj4es 7 ай бұрын

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

@saneeshkrishna3818 Жыл бұрын

You are amazing brooooo I want to become expert in power query. What should I do??

@subhajitdutta8386 11 күн бұрын

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

@yam5400 3 ай бұрын

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?

@yoginathbadadale Жыл бұрын

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