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

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.

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

🚩Let’s connect on social:

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

[IvanCortinas_ES]

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!

[pamm]

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!

[BGPAPA]

If my accounting teacher was half as good looking as you I might have actually showed up for class!

[alexkim]

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.

[lisac]

I'm so glad I found your Youtube 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.

[wayneedmondson]

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

[markmanno]

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

[MrSupernova]

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!

[emmanuelle]

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

[jbenedict]

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.

[dirkstaszak]

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 😊

[rockpaperwaterscissor]

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

[arjunps]

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.

[nataliefino]

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

[mbhla]

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

[maduwin]

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

[MdAbdullahAlMuyid_nowho]

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

[paolosoloperto]

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

[abobobilly]

You make it look so easy. Absolutely wonderful 🥰

[psngxot]

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!