Data Analytics with Excel Power Query: SAP JE Analytics Part 1 – Preparation of GL accounts

I am getting deep into data analytics and want to share with you guys an example of Data Analytics I recently did with SAP journal entries. The whole exercise is split into three posts due to steps needed to build the whole file. In case you haven’t worked with Excel Power Query before, I recommend you to read two earlier posts about how to get started with Power Query:

Read the posts from here:

Here’s in the nutshell what these three posts will do for you:

  1. Part 1 (this post): 
    • We  extract from SAP General Ledger accounts and clean it with Power Query
  2. Part 2:
    • We extract from SAP Journal Entry listing and clean it with Power Query
  3.  Part 3: 
    • We transform/combine/merge/append two sets of data above and finally do some analytics with the data

I took time and it took me around 4 hours to make the final file. Before the team was doing this analysis from the scratch for every audit. It easily took 4 hours in a traditional way in Excel to clean/filter/Sort/delete to make files workable and get to the “final stage”. After you’ve invested 4 hours to make our example file, it will take you 5 minutes to reach “final stage”.  So let’s get started…

 

STEP 1: Prepare folder

In order to use our amazing file in the audits later on, I’ve created a folder which I’ll be using. At the end of this post there will be three files in that folder:

  1. ACCOUNTS.txt
    • This is the listing of GL Accounts from SAP printed out as .txt. SAP transaction I’m using is: S_ALR_87012328
  2. JE.txt
    • This is the Journal Entry listing from SAP printed out as .txt. SAP transaction I’m using is: S_ALR_87012291
  3. PQ.xlsx
    • This is the actual Power Query file reading the two files above

I’m not going to go through how to extract the files from SAP. But below are screenshots who the files look like in notepad just to give you an idea:

GL Accounts:

JE listing:

2. STEP 2: Prepare GL Accounts file with Power Query

1. Open Excel -file and go to Power Query and open file from .txt -file. In my case, the delimiter is “Tab”. Click edit

2. Keep only column 2,3,4 and remove the rest

3. Fill down column 3. This function means that always fill down the values until there is a value in the cell

We want to have in our file Columns “Account Number” and ” Account Name”. You see from the screenshot above that Column 2 row 10 contains both of that information.

4. Filter column 2 only two show “Long Text”. This will visible only the rows where we have needed information

5. Remove Column 2 (yes you can do this in Power Query without affecting the filtering in the step above) and rename Column 3 and Column 4

6. Chage type in Account number to “Decimal number”.

Power Query and Excel are quite picky when it comes to cell types and it’s better that whenever you work with number-only columns, use “Decimal number” type

7. Close and Load.

That’s it! Make sure that you don’t see any errors on the Power Query pane on the right

Now we have our GL accounts -file cleaned and ready to be used in our data analytics for Journal Entries.

 

Leave a Reply

Your email address will not be published. Required fields are marked *