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