How To Import & Clean Messy Accounting Data in Excel | Use Power Query to Import SAP Data

Подписаться 2,6 млн

Просмотров 539 тыс.

50% 1

Get Access to My Course "Fundamentals of Financial Analysis" HERE: www.xelplus.com/course/learn-...
This video is a goldmine for accountants who frequently work with data from systems like SAP and Oracle. It introduces a powerful Excel tool, Power Query, which acts as a 'magic box' to clean and organize data with minimal effort. The video showcases practical examples, particularly focusing on importing and transforming SAP data for financial reporting.
Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
✨ Key Topics Covered:
▪️ Importing SAP Data into Excel: Demonstrates how to import an SAP income statement extract with European formatting into Excel and prepare it for analysis.
▪️ Power Query Transformations: Learn to merge columns, filter out unnecessary data, and correct number formatting issues using Power Query.
▪️ Creating a Clean and Analyzable Report: Step-by-step guide on transforming raw data into a report comparing current year-to-date information with the previous year.
▪️ Automating Repetitive Tasks: Discover how Power Query can automate steps for future data updates, saving time and effort.
▪️ Additional Example - Creating Pivot Tables from Messy Data: Learn how to use Power Query to prepare data for pivot table analysis, including cleaning and organizing the data.
00:00 Data Cleaning with Excel for Accountants
00:40 How to Import SAP Data in Excel with Power Query
15:43 How to Create a Pivot Table from Messy Excel Data
20:02 Wrap Up
Struggling with messy Excel data? 🤔 This video is a lifesaver for anyone, especially accountants, who regularly import and clean data from systems like SAP and Oracle. We dive into a powerful Excel tool, Power Query, to make your data management a breeze!
👩‍💼 Perfect for:
- Accountants and finance professionals.
- Anyone dealing with frequent data import and cleanup.
- Excel users looking to save time and increase efficiency.
📌 Key Highlights:
- Transforming European data formats to US settings.
- Creating a summary report with Power Query.
- Using 'XLOOKUP' and 'VLOOKUP' for precise data retrieval.
- Cleaning and preparing data for pivot table analysis.
LINK to more Accounting tutorials: • Unleash your inner Fin...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#Excel #accounting #sap

Опубликовано:

17 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 418

@LeilaGharani 5 месяцев назад

Learn tips like these and much more inside our "Fundamentals of Financial Analysis" course - click to join and start learning today! 👉www.xelplus.com/course/learn-financial-analysis/

@IvanCortinas_ES 3 года назад

An excellent job Leila. Let's hope that senior management (usually with little knowledge of data analysis) watch your video and open their eyes. Thank you!

@pamm8439 3 года назад

I have used your videos to learn power query and introduce so many efficiencies for my team. Converting the currency with locale was so useful in automating a bank statement import from Euro format. Thank you for these videos!

@markmanno2054 3 года назад

Thanks for another great video Leila! I just started your Power Query course and so far it is fantastic!

@lisac7386 3 года назад

I'm so glad I found your RU-vid channel. I am doing some volunteer work and previously it took our volunteer treasurer 5+ hours to make the summary financial report that are distributed monthly. Your videos have allowed me to develop my basic Excel skills and automate and upgrade the reports. This video will help in the still time consuming task of cleaning up our source data. Thank you so much. I do have one problem - as I transfer values about, my formatting changes. Certain reports have unique formats due to their layouts, and the same information goes into different reports so setting the source format to match the final destination format doesn't work. If you point me in the right direction for this I would appreciate it.

@MrSupernova111 2 года назад

Brilliant!! I work in finance and every reporting period we have to merge about 20 data tables, group the data by classification, and generate a client report by groupings which vary per client. About a year ago I created an Excel template using nothing but Excel functions. While the template works fine because there are only about a thousand rows of total data, updating the file manually each quarter takes a while because there are about 20 tables that must be added/appended manually. Also, the output page was challenging to create as I need to filter out blank rows and maintain the integrity of each grouping. Imagine a single client report with up to 20 data tables for each grouping. I'm going to re-haul the template using Power Query so in the future all we need to do is drop the client performance data and have Power Query merge and transform the other data directly from the source files (about 20) like you did here. Thank you for another amazing video! Cheers!

@rockpaperwaterscissor4624 3 года назад

Great video as always, this is what I have been looking for quite sometimes, thanks Leila!

@emmanuelle8032 2 года назад

THIS is just an amazing presentation clear, precise and so useful. Thank you very much Leila.

@wayneedmondson1065 3 года назад

Awesome lesson Leila! Power Query is super useful for so many things. Thanks for the demo of how to expand it to tasks beyond just combining tables. Thanks for sharing and Thumbs up!!

@ngapunchan8116 Год назад

😘

@alexkim7270 3 года назад

I've never worked with merge columns before. Thanks for sharing this golden nugget. This surely would make my life much easier than writing if statements.

@LeilaGharani 3 года назад

You're very welcome, Alex!

@arjunps6776 3 года назад

What a fantastic tutorial! I get files with European numbering system and I used to use to Substitute function in Excel to replace comma with dots and vice versa. Thanks to this video, I'll start using Power Query now.

@MrSupernova111 2 года назад

That's a good idea! Power Query also has a "replace" feature. Many ways to do this one.

@madu2win382 3 года назад

Thanks Leila for your tips...Hope You, fmly and all team always healthy and happy to help

@jbenedict 2 года назад

never used power query before but i do data cleanup manually. will do this next time as we always receive revised data from our clients. this is far more efficient! Thank you so much. Big 4 auditor here. yeah wish me luck this busy season.

@markbainbridge3240 3 года назад

Leila Many thanks for this, that really showed me how Power Query could help me as an accountant. I am enjoying your Power Query course on Udemy as well. Hopefully I will be an Excel power user soon.

@dirkstaszak4838 2 года назад

Hi this is actually what I am doing in my daily business. I would however strongly recommend to use Excel export from SAP as it is more reliable. Otherwise it is always interesting to see the various approaches to a particular issue. Thanks 😊

@mbhla 3 года назад

Ausgezeichnet! Vielen Dank! This is a powerful time saver for all of us working with ERP data dumps.

@vijalchokshi632 3 года назад

Great Video Leila, appreciate all your great work!!

@paolosoloperto4948 3 года назад

Just one word .... Leila you are a genius! congratulations on your videos

@abobobilly 3 года назад

You make it look so easy. Absolutely wonderful 🥰

@AhmedIbrahim-vl6bv 3 года назад

As usual, Fantastic. I believe many people would like to learn more about these kind of tasks. I highly recommend Leila's course "Power Query - Including M". Truly a masterpiece.

@abughaida4855 3 года назад

I really like the way you explain it, you are the best, really appreciated.

@sachinrv1 3 года назад

Fantastic video Leila. The Hot tip is at 14:50 frame. Not many people know that one can in fact do find and replace inside the formula. One can do find and replace the cell ranges in the formula as well. That's really pro way of using Excel. Thanks Cheers :)

@LeilaGharani 3 года назад

Thanks Sachin!

@MrSupernova111 2 года назад

I didn't know I could do that. It would have been handy many times in the past!

@salongirl77 3 года назад

AMAZING video. Data cleansing has been an ongoing nightmare and now I’m looking forward to using all your tricks.. nearly everything you covered will come in handy... THANK YOU

@LeilaGharani 3 года назад

Wonderful!

@MdAbdullahAlMuyid_nowho 7 месяцев назад

This is the closest video on ERP generated data. Huge thumbs up for introducing these clean up methods. Thanks a ton

@simbarashemakwati4260 3 месяца назад

I enjoyed the fill function on Account. I wish I could use that. However data has account in the same column as date i want to remove I want to put account into its own column and date in its own column. How do I do that?

@nataliefino1926 3 года назад

Leila, no one else I have found explains Excel as well as you do. Thank you!!

@LeilaGharani 3 года назад

Glad it was helpful, Natalie!

@vxs6052 2 года назад

I dont know why I watched this, I know how to do everything you've just explained. However..... Your videos are presented so well, the window/screen positioning and helpful Keyboard shortcuts and Highlighted overlays markers you put up make following these tutorials so good and helpfult o noobies. Great work! 👍

@qiaorongchang8605 3 года назад

Your tutorial never been disappointed. Accounting life will be easier with it. Thanks goddess Excel!

@LeilaGharani 3 года назад

You're so welcome!

@3choff 3 года назад

Very useful and clear tutorial. Thank you!

@AetasVolat 3 года назад

Great Video Leila, thanks for your effort. I would like to see more Videos on how to use excel for sap-related issues.

@adarshv14 Год назад

Thank you so much Leila!!! You’re the best!

@ravvenhawk1110 2 года назад

Thank you very much, you are The EXCEL ANGEL you changed my perception toward excel and data analysis, I am grateful that I meet you on RU-vid, and i am thinking of subscribing to SkillShare to follow your courses and interact with you for some coaching, Thank you very much Leila.

@jakubspevacek3357 3 года назад

Useful and inspiring as usual. Thank you.

@yunanilyas9595 3 года назад

From all video i watch from your channel, i like this most. You explain it in a more constant tempo and not to fast. Thanks.

@LeilaGharani 3 года назад

Glad to hear that!

@ahsanlive Год назад

Every day I do in my office place, I do same thing by manually data export to excel from Oracle database by "copy and move" making another worksheet not to destroy original format, Will try this time your rules and format, hopefully I will manage it. Thank you for your wonderful lesson and video.

@samkhaiber4263 3 года назад

Very useful and well explained, Thank you Leila jon.

@sandorszilagyi5929 3 года назад

Thank you for this excellent tutorial !

@rmmccarthy1240 2 года назад

Fantastic! Thank you for the lesson!

@vilassadhwani5335 3 года назад

You are definitely goddess of MS Excel😇

@vocabularyplusbd 3 года назад

I'm really pleased with your teaching.💙

@DreamFirms 3 года назад

Hey Leila, this content is very helpful!

@thesandman8642 2 года назад

Great presentation on Power Query Leila. Saving hours of manual work.

@yulinliu850 3 года назад

Super useful skills. Thanks Leila!

@adrianbarisano 3 года назад

This is amazing! Could you make a tutorial explaining how to connect an excel sheet or power query to an oracle database and do the whole transformation and cleaning process without the need of download the .txt file?

@SholaDsdg 3 года назад

This is well-presented and taught-out! Just what I need Leila!

@LeilaGharani 3 года назад

Our pleasure :)

@jdfoote1314 Год назад

❤❤❤❤❤ Five out of five hearts! Thank you. This will save me HOURS per month!

@waleadeniji6959 2 года назад

Thank you Leila this tutorial is amazing.... i just need to practice is more

@somfydubai2906 3 года назад

Excellent Tutorial, Leila you are really brainy, you saved my time of dirty laundry washing (reports from our ERP)

@RajeshBhonkiyaTrainer 3 года назад

always rocks! your every tip are like a swiss knife everywhere we can use it , best wishes always

@sathyajithmenon1384 3 года назад

What a marvelous way of explanation.......... Excellent ....... Well done.... thanks a lot.............. loads to learn from you

@ImranHussain-xv4se 2 года назад

Great, I m SAP user , I was looking for this material Thanks

@mattschoular8844 3 года назад

Thanks Leila.. Power Query is fantastic. I use it everyday in my workflow. Great tutorial.

@LeilaGharani 3 года назад

It really is! Thanks, Matt!

@kasiafitko7057 Год назад

Its really great ! Thanks a lot for this video!

@mosadiq123 3 года назад

Dear Leila, Thanks a lot for sharing the amazing knowledge with us. I really enjoyed the video and learned a lot. Once again Thank you so much for your need of the hour video.👍👌🙏

@LeilaGharani 3 года назад

My pleasure!

@lorenzobattistin1501 3 года назад

Thank you Leila! Great video that will help people to save loads of time!

@LeilaGharani 3 года назад

Glad you think so, Lorenzo!

@jerry5149 3 месяца назад

You use very good techniques, impressive.

@exceltutoredwithanass7449 2 года назад

That was the most fantastic 20 min video on cleaning Data, and also includes a lot of tricks for fast work I always enjoy ur videos and waiting for more like this one. Thank u a lot for that.

@LeilaGharani 2 года назад

Glad you liked it!

@samuelkodjoe1645 3 года назад

You just saved me from a tedious task. Thank you!!

@mrmusicloverable 3 года назад

Powerful tutorial. Thank you Laila.

@moyoonanuga4495 2 года назад

I needed this. Thank you!

@SyedMuzammilMahasanShahi 3 года назад

Thanks for this wonderful video. Going to help a lot of people.

@patriciomoenne7119 Год назад

It´s wonderful and very useful information. Thank you very much !

@rebeccak-d8561 Год назад

Thank you!! This is so helpful for my current task!!

@harisamu3620 2 года назад

I love to listen your classes... Thank you❤

@aftabalam-aiza 6 месяцев назад

Excellent. This lecture is most relevant to me as a financial management specialist. Thank you Leila Gharani.

@LeilaGharani 6 месяцев назад

You're most welcome!

@benzackry 3 года назад

Thank you so much for this!

@oe4188 2 года назад

Your content Is just amazing and so relevant

@zaydarendse2812 3 года назад

I had no idea one could use ''Find and Replace'' in a formula!! Thanks for Sharing! :)

@vijayarjunwadkar 3 года назад

Wow, Excellent Video! 👌 Thanks for sharing some super ideas for setting up data that are surely going to help me a lot! Waiting for more!😊👍

@LeilaGharani 3 года назад

Thank you!

@erictsang789 2 года назад

Super ! I have joined the courses, the course is so great, response to your queries promptly. Of course, depends on your subject. Eric

@mamadoubah3686 2 года назад

Great content! Thank you so much!

@alterchannel2501 2 года назад

Fantastic like all your videos! PLEASE more tutorials on importing SAP data in excel.

@OliverPohl 2 года назад

Wow, it's magic... I have to askmy boss for a laptop for home working So I can practice this right here... I love it :-)

@helen1599 3 года назад

Such a pleasure to see that even black belts like Leila still manually check wether column is really empty. :-D I often blame myself for big amount of hand clicks.

@silverlicious2086 3 года назад

Nice examples Leila. Very detailed. Great job! Have a nice weekend.

@LeilaGharani 3 года назад

Thank you! 😃

@petecullan3113 3 года назад

Wow. Very helpful. Thank you!

@user-ps6ng7xo9t 3 года назад

It would be cool if you uploaded a file with data from the example.When you do everything with your own hands, it's better to remember.Amazing Leila.Amazing video.Thank you!

@MrSupernova111 2 года назад

You can pay for her course and get her training materials.

@renatovianello874 2 года назад

@@MrSupernova111 Which course you mean? I have subscribed Leila's Master Excel Power Query Beginner to Advanced (including M) course, but this training material is not included.

@akkithelegend Год назад

Hi you can practice from another video, where all the case studies are practically executed - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-cx1qosaiLu8.html

@grendbelema 3 года назад

awesome...as usual, Thanks Leila

@loidaasar487 3 года назад

Absolutely great!

@raymondgwati8559 2 года назад

Well done Leila very impressive. Thank you so much I'm learning a lot from you hey

@Madd0g5148 3 года назад

Awesome. Will try use this for other applications. Thanks

@RA-rh5lb Год назад

informative information. Thank you so much Leila.

@suleymaninan5556 Год назад

Hi, Leila. Harika anlattınız 👏

@meezonabulsi7185 11 месяцев назад

great learning love to do cleaning data

@TheJaebeomPark 2 года назад

I feel lucky to see your video!

@eddiegarner897 3 года назад

Amazing, great vid, very clear, love the format of your videos. Loved the correction on the vid highlighting the correct spelling of the description typo 10:54... shows what you're all about... Appreciated :)

@LeilaGharani 3 года назад

Thanks for the kind feedback, Eddie!

@anjursi5665 2 года назад

Thank you and god bless you miss. greeting from Indonesia

@emwrmi 3 года назад

Thank you for a great presentation

@chh8860 3 года назад

Sooooooooooo much to think about .... how can one person deliver sooooo much information so effortlessly? .... so good ... so good ... so good ... thank you ...thank you ... thank you ...

@LeilaGharani 3 года назад

Wow, thank you!

@silvanodemetrio407 2 года назад

Great. Thanks for sharing your knowledge with us.

@ravingagunawardana178 3 года назад

Thank you! Your videos are awesome!

@LeilaGharani 3 года назад

Thanks for watching!

@searchbug Год назад

Thanks for the great tutorial, Leila! But just in case others are trying to find another way to clean data, especially contact list, data appending will do the job instantly.