Excel - Power Query - Bank Statements - Combining Data based on Date Values, Type, Descriptions

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 

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