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
- Amount in LC
- User name
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”
- Remove empty
- 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:
- Pstng Date – Date
- Entry Date – Date
- Doc. Date – Date
- DocumentNo – No change
- Account – Decimal Number
- LCurr – No change
- Amount in LC – Decimal Number
- User name – No change
- CoCd – No change
- Itm – No change
- Tx – No change
- 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.