Data Analytics with Excel Power Query: SAP JE Analytics Part 3 – Making the Analytics

Finally we have reached the stage where the real fun starts! Now it’s time to do the data analytics part for the journal entry listing from SAP.

You can read the Parts 1 and 3 from here:

In this part we will do the following

  1. Bring GL Accounts name from Accounts query to JE query
  2. Create data analytics checks to highlight possible issues
    1. Entry and Posting in different months?
    2. Posted on Weekends?
    3. Possible Duplicate?
  3. Create Pivot tables to have all the possible flags nicely organized in one sheet in Excel

STEP 1: Bring GL Accounts name from Accounts query to JE query

Open the JE Query and click “Merge Queries”.

  • What we do here is to Merge “Account” columns from both “JE” and “Accounts” -queries and bring the “Account Name” from “Accounts” -query to “JE” -query.
  • Make sure that “Join Kind” is “Left Outer”

  • After click “OK” in the screenshot above, you’ll notice that new column was created and that every row has a value “Table”
  • There are two rows pointing outwards, click that and you’ll see what information you’ll want to show in that column (options to who are from the column you merged, “Accounts”
    • Select “Account Name” and click “OK”
    • You can also rename & move the new column, if you want to

  • Check your results, by clicking “Close and Load”

STEP 2: Create data analytics checks to highlight possible issues

Entry and Posting in different months?

Create two custom columns to show months for entry date and Posting date.

  • Formula is Date.Month([Column]).
    • Select the “Pstng Date” -column from the list on the right.
    • Note that this function will result an error, if “Pstng Date” & “Entry Date” columns are not Date -type (i.e. text -type will result an error)

Then we need to the “check” whether the entry and posting has been in the same month.

  • This is very simple calculation: Posting Month -column LESS Entry Month -column
  • Create a new custom column and simply in the formula select from the list on the right first “Posting Month”, then add “-“, and finally select “Posting Month”

  • Then create conditional column
    • If column we just created (I named this as Entry_postin_1 as it will be deleted in a moment) does not equal 0 then ISSUE, Otherwise OK
      • We want to check if entry and posting was in the same month, so value 0 = they were, value not 0 = they were in different months = possible ISSUE

  • Delete the “Entry_posting_1” column as it’s not anymore needed
    • Remember that with Power Query you can remove columns that were used earlier without destroying formulas in the later stages!

Posted on Weekends?

Create a new custom column with the following formula:

  • Date.DayOfWeek([Entry Date]), Day.Monday)
    • What the formula does is: It returns the day number calculated from Monday (Monday = 0)
      • E.g. Tuesday = 1, Wednesday = 2 etc.
      • Saturday and Sunday are 5 & 6

Then we create a conditional column to higlight if entry has been posted on weekend. See below the rules we need to implement. After creating the conditional column, you can remove the column created in the aboev step.


Possible Duplicate?

In order to see if entry is a possible dublicate, we need to create a combined cell where posting month, account and amount are the same.

You can do this by selecting columns “Posting Month”, “Account” and “Amount in LC” and then clicking “Add Column” -ribbon and “Merge Columns”

Now we need to create another query in order to calculate how many times “Month_Account_Amount” appears in the JE listing.

Create new query like we did in Part 2 of this series (yes from the beginning by making a query from JE listing)

When you have the second query ready, repeat the step above to create “Month_Account_Amount” -column. Below you’ll see what we need to have visible in the second query

Now we can calculate how many times “Month_Account_Amount” -combination appears in the JE listing. This is done with “Group By” -function. After clicking “OK”, where ever the value is more than 2, it means that the “Month_Account”Amount” is found multiple times and hence is a possible dublicate. You can now “Close and Load” this query.

Last thing to do is to merge queries together and to highlight whether there is a possible dublicate

Finally create a conditional column to show either “ISSUE” or “OK”. Don’t create the column above as it’s needed in the Step 3!

STEP 3: Create Pivot tables to have all the possible flags nicely organized in one sheet in Excel

Now we have all the calculations done. You should have following columns visible in Excel after closing and loading the query editor

Since all our Data Analytics columns are in form of “OK” or issue we can now create Pivot tables to show only JEs with potential issues.

Put report filter always to be the column with values “ISSUE” or “OK” and select to show only “ISSUE”. Below are the selections I used for my analysis

Note that in the last one I didn’t have any JEs where entry and posting would have been in different months and thus, there’s nothing to be flagged.

And that’s it! Now we have identified the journal entries we want to do the testing with.

The beautiful thing is that going forward, I just extract new set of data from SAP, name the files as “Accounts.txt” and “JE.txt” and paste them into this folder. Then I open the PQ Excel file and open Power Query Pane and just refresh all the three queries we did in this tutorial-series. Finally I refresh the Pivot tables and I have new set of Analytics done!



Leave a Reply

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