Data Analytics with Excel Power Query: SAP JE Analytics Part 2 – Preparation of JE file

In the second part of the Data Analytics tutorial with Excel Power Qyery for Journal Entries, we’ll clean and transform the JE listing from SAP.

SAP transaction I’m using is: S_ALR_87012291 and below is a screenshot of the text file from SAP.

Let’s get to it!

STEP1: Open the text file with PowerQuery from the same file we created in Part 1 tutorial

You can read the Part 1 here: https://www.lazyauditor.com/2018/02/22/data-analytics-excel-power-query-sap-je-analytics-part-1/

Note that it’s advisable to name the file as “JE.txt” so redoing the analytics with new set of data is easier.

Open the txt file with PowerQuery (from file -> txt) and then make sure that delimiter is “tab”. Click “Edit” to open the PQ editor

STEP2: Cleaning the file

1. Make row 4 to be header

  • Remove TOP3 rows
  • Use first rows as header as seen in the screenshot below

2. Remove useless columns

  • Right-click column you want to delete and click “delete column”
  • Columns we will need to keep:
    • Pstng Date
    • Entry Date
    • Doc. Date
    • DocumentNo
    • Account
    • LCurr
    • Amount in LC
    • User name
    • CoCd
    • Itm
    • Tx
    • PK

3. Filter out useless rows

There are two things we need to do to keep only rows with dates in the first column; “Pstng Date”

  1. Remove empty
  2. By using text filters, keep only rows that contains “.”

4. Change, as needed, the “Amount in LC” -column

  • In my Excel, I don’t have thousand -separator as “.” and comma separator is “.” instead of “,”
  • I need to make “Amount in LC” to work by replacing firstly “.” with ” ” (i.e. empty” and secondly “,” with “.”

 

5. Change type of the columns

We need to have the types for the columns as following:

  1. Pstng Date – Date
  2. Entry Date – Date
  3. Doc. Date – Date
  4. DocumentNo – No change
  5. Account – Decimal Number
  6. LCurr – No change
  7. Amount in LC – Decimal Number
  8. User name – No change
  9. CoCd – No change
  10. Itm – No change
  11. Tx – No change
  12. PK – No change

You can find “Change type” function by right-clicking the column

STEP 3: Load into Excel to check that file loads successfully

Click “Close and Load” and make sure that you see in normal Excel no errors in PQ pane (see below)

And that’s it!

In the final part of this mini-series tutorial, we will do the data analytics that requires merging the files created in Part 1 and Part 2. We’ll also do some analytics that will automate sampling for Journal Entry testing.

 

Leave a Reply

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