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 Max,

This is the code for the above. Just add it as a separate query (New Source->Blank Query)

and replace the content of the new query with the code below, then replace the Source ste pcontent to a reference to your original query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zVbbbtswDP0VI8+pKlH3Pk1x3MZD4gS2s6Ho+gfF3vr/k2Q7YRw7S5wO2ItcpCQPKR4e6u1tRtkjY49AmZrNZ78/Pz78Z+HWrkizZFFu9y+rOnnelj9duTwatB9tiJy9z30QgYL8yCt/rlxepvv6zCd+gs/pz+tt6up8W/g/mR23bmLvyqyqkmq13U0Ib4lFAMISyZoa9FkNeVH/+qQU9DrxpxTCyGsB0zJzdZKur7U3lD4YkA9KGRHSkkTz43850aZJ0qIkl6EjdYBgJHq11oxYEa2Z7lv7irKyiFfhoiOXRBrkyjlRIlk27ga5p2U4tpudQ9ZKaULFQFURbJm/5HWE4YCc5pIyImg0B9bPcLdOr72znXvdZEWgmKQh7XGHGDltKKxAh5Yf71ZTAg2NYYDGmVvXqyks9l1AbOHSEN6AyDOQrEj8pNXZFBiMIXiHgRu/CFNSuE2WhOPGyw3pMT/nArGRKUGMbvjo+wcj6uHvu8yz5ah6XBnmbyKEw2gU5u4RrvaLKs137UV/wRQzZgkThzmG8zkGOBEnqwhrmOmN+5VNlD9x0krLiIYGQSGEoWEHytthhzkDQ7Rq/M4qOdMYgQVmbg3tfIH2fcckw4oDILC+UyMZQQOExE7C3z9vxB1436lVA2De64JyuHoTjaubNSD5xuAJWLw5nBV4ZWj5CuarutptM99OO+BwV2x+mj54qVXd1NKoMnrC8F8b6K/jfxKIo0AH3ZsgeYqiohUCkAjgLm7op0hWzA1FjzhqECeZDsQQkLRHIIuARoVomE//zfNIgjg8eQDVczvtX/ZeuqrsdRZfURZDQNjnrVLeA4GXN+EnEIxI1T3cvgyCGQxBCTfd467X+Iu63Ue6oNbjEpp8dwXrB72BwxJxGOyxFPtvbguMJdAtqiPC4EtYU0WoGgCpn4O5VEbKREkLXPa3zWhidemK6jkr4/RiQbJWElDdYuu1cXgbDvav3YFcWsIv7MBbpZ0DEX7Bvf8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Transaction Type" = _t, #"Payment type and details" = _t, #"Paid out" = _t, #"Paid in" = _t, Balance = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Transaction Type", type text}, {"Payment type and details", type text}, {"Paid out", type number}, {"Paid in", type number}, {"Balance", type text}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Date"}),
    #"Fix Numbers" = Table.ReplaceValue(#"Filled Down",null,0,(x, y, z) as number=> if x = null or x = "null" then 0 else Number.From(Text.Remove(Text.From(x), {"A".."Z"} & {"a" .. "z"} & {" "})) ,{"Paid out", "Paid in", "Balance"}),
    #"Fix Types" = Table.ReplaceValue(#"Fix Numbers","null","",Replacer.ReplaceValue,{"Transaction Type"}),
    #"Added Conditional Column" = Table.AddColumn(#"Fix Types", "Filter", each if [Paid out] + [Paid in] + [Balance] <> 0 then true else false, type logical),
    Process = List.Skip(List.Accumulate(Table.ToRecords(#"Added Conditional Column"), {[Filter = false, Count = 0]}, (a, n) => a & {Record.AddField(n, "Count", List.Last(a)[Count] + (if List.Last(a)[Filter] then 1 else 0))})),
    Format = Table.FromRecords(Process, Value.Type(Table.AddColumn(#"Added Conditional Column", "Count", each null, type number))),
    #"Grouped Rows" = Table.Group(Format, {"Count"}, {{"Date", each List.Last([Date]), type nullable date}, {"Transaction Type", each List.First([Date]), type nullable date}, {"Payment type and details", each Text.Combine([Payment type and details], " "), type nullable text}, {"Paid out", each List.Sum([Paid out]), type number}, {"Paid in", each List.Sum([Paid in]), type number}, {"Balance", each List.Sum([Balance]), type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Count"})
in
    #"Removed Columns"

It works on my side, hopefully, will work on your side too.

I am not sure that I can help to resolve the issue with importing PDF's second page from my side, this may require some experimenting. If the date would not be missing competely, I think I could do something to format it right, but if this does not come through to PQ, I do not know even what to start with :(. Maybe try playing with the import function paramaters?

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

Hi jbwtp 

Many thanks for your message.

I have added the new source code and this has created a new name as "Query1" , and the result is shown below. 

Question: I need some further clarification on how to implement the step as per your instruction below in Red to update the transaction descriptions to the source folder data:

and replace the content of the new query with the code below, then replace the Source ste pcontent to a reference to your original query:

Regarding the secon pages not pulling through, I may have to just add these in manually when reviewing the data.

Many thanks for your assistance in this as its greatly appreciated.

Cheers Max

Hi Maxitco,

Sorry for the delay, was busy with another deadline.

In your case (as I can see on the screenshot) the first step would look like:

Source = #"HSBC Advance Account" // which is the name of your existing query, which returns the list of transactions from the statement

cheers,

John