Forum Discussion
Excel - Power Query - Bank Statements - Combining Data based on Date Values, Type, Descriptions
Hi Max,
I think we need to break it into digestable pieces.
Could you please delete everything past the VIS step. only leaving these ones:
Make sure there is no errors in the output and then copy and past the data in the Excel file (click in the table sign in the top-left corner):
Edit the data in Excel to remove the sensitive info (in the payment type and details column), but ideally leave the strucutre (e.g. if you have something like Mr John Doe, change it to Mr Don Duck, but leave the text, so I could see what to expect in the column).
Then copy and past the table from Excel to the forum (as text and not as picture, please). I will see what needs to be done to transform it into the desired output.
Please note, that I've started an urgent project that we need to complete before Xmas, so from this week my responses can be slow (or may be I will not be able to reply until Jan), sorry.
Cheers,
John
Hi jbwtp
Thank you and I understand. Thanks for your help.
Please see below the raw data from 3 statements which I have copied as text. I have before deleted the other steps and left what you have advised.
Note that there are more transactions after 23/01/2017 which go onto a second page in the statement, however as some of the transaction form part of the 23/01/2017 and there is no date to bring them into Power Query, they are jus missing. So the import is not grabbing this additional data from the PDF. Not sure why this is the case.
I have kept the format for the description and details and removed personal details and replaced with fictitious information. The cell formats remain the same.
Note, as you may remember, that there are additional description lines which can be merged into one line and this can be seen by the transaction type reference as there may be more than one transaction for each date; and also that additional transactions do not have a date associated with it and show as null.
Cheers
Max
| Date | Transaction Type | Payment type and details | Paid out | Paid in | Balance |
| 01/11/2016 | null | BALANCE BROUGHT FORWARD | null | null | 78.5 |
| 04/11/2016 | VIS | HAIRCUT | null | null | null |
| null | null | LOCATION | 19 | null | null |
| null | VIS | PRESS SHOP | null | null | null |
| null | null | LOCATION | 9.99 | null | 49.51 |
| 07/11/2016 | VIS | INT'L 00254485 | null | null | null |
| null | null | CREAT CL | null | null | null |
| null | null | 800-825-6684 | 45.73 | null | 3.78 |
| 09/11/2016 | DD | TL | 1.84 | null | 1.94 |
| 17/11/2016 | DD | INTERNATIONAL | 135.58 | null | 133.64 D |
| 18/11/2016 | CR | COMPA | null | 1667.04 | null |
| null | DD | DIGITAL | 32 | null | 1,501.40 |
| 21/11/2016 | DD | PLC | null | null | null |
| null | null | PAYMENT | 503.64 | null | null |
| null | DD | CARD | 627.51 | null | 370.25 |
| 24/11/2016 | VIS | HEALTH | null | null | null |
| null | null | LOCATION | 11.95 | null | 358.3 |
| 25/11/2016 | VIS | EN ROUTE | null | null | null |
| null | null | LOCATION | 15 | null | 343.3 |
| 27/11/2016 | BP | NAME NAME | null | null | null |
| null | null | PAYMENTS | 178.43 | null | 164.87 |
| 01/12/2016 | null | BALANCE CARRIED FORWARD | null | null | 164.87 |
| 01/12/2016 | null | BALANCE BROUGHT FORWARD | null | null | 164.87 |
| 07/12/2016 | VIS | INT'L 00254485 | null | null | null |
| null | null | SUBSCIPTION | null | null | null |
| null | null | 800-825-6684 | 45.73 | null | 119.14 |
| 09/12/2016 | DD | TL | 22.99 | null | 96.15 |
| 12/12/2016 | VIS | SHOP | null | null | null |
| null | null | LOCATION | 4.43 | null | 91.72 |
| 16/12/2016 | CR | COMPA | null | 2037.04 | 2,128.76 |
| 19/12/2016 | DD | INTERNATIONAL | 148 | null | 1,980.76 |
| 20/12/2016 | DD | DIGITAL | 32 | null | 1,948.76 |
| 21/12/2016 | DD | PLC | 503.45 | null | 1,445.31 |
| 23/12/2016 | DD | CARD | 213.44 | null | null |
| null | ATM | CASH | null | null | null |
| null | null | LOCATION @12:21 | 20 | null | 1,211.87 |
| 28/12/2016 | VIS | SHOP | null | null | null |
| null | null | LOCATION | null | 41.79 | null |
| null | VIS | SHOP | null | null | null |
| null | null | LOCATION | 30 | null | 1,223.66 |
| 01/01/2017 | null | BALANCE CARRIED FORWARD | null | null | 1,223.66 |
| 01/01/2017 | null | BALANCE BROUGHT FORWARD | null | null | 1,223.66 |
| 03/01/2017 | BP | NAME | null | null | null |
| null | null | PAYMENTS | 600 | null | 623.66 |
| 05/01/2017 | ATM | CASH | null | null | null |
| null | null | LOCATION @17:45 | 20 | null | 603.66 |
| 06/01/2017 | ATM | CASH | null | null | null |
| null | null | LOCATION @17:45 | 10 | null | 593.66 |
| 09/01/2017 | DD | TL | 22.99 | null | null |
| null | VIS | INT'L 00254485 | null | null | null |
| null | null | CREAT CL | null | null | null |
| null | null | 800-825-6684 | 45.73 | null | 524.94 |
| 12/01/2017 | VIS | SHOP | null | null | null |
| null | null | GUERNSEY | 1.99 | null | 522.95 |
| 16/01/2017 | VIS | SHOP | null | null | null |
| null | null | LOCATION | 1.39 | null | 521.56 |
| 17/01/2017 | VIS | SHOP | null | null | null |
| null | null | LOCATION | 1.18 | null | 520.38 |
| 18/01/2017 | DD | INTERNATIONAL | 148 | null | null |
| null | DD | DIGITAL | 32 | null | null |
| null | ATM | CASH JAN18 | null | null | null |
| null | null | LOCATION @17:45 | 50 | null | 290.38 |
| 19/01/2017 | VIS | SHOP | null | null | null |
| null | null | LOCATION | 1.18 | null | 289.2 |
| 20/01/2017 | CR | COMPA | null | 1706.06 | null |
| null | TFR | 56855 6592354 | null | null | null |
| null | null | TRANSFER | 1000 | null | 995.26 |
| 23/01/2017 | DD | PLC | 503.45 | null | null |
| null | DD | CARD | 359.34 | null | null |
| null | null | BALANCE CARRIED FORWARD | null | null | 132.47 |