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

Hi Maxitco,

Please try if the below works:

let
    Source = Folder.Files("C:\Users\HSBC Excel Power Query - Accounts Download\HSBC Advance Account"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Date", type date}, {"Column2", type text}, {"Payment type and details", type text}, {"Paid out", type number}, {"Paid in", type number}, {"Balance", type text}}),
    #"Removed Source Name Column" = Table.RemoveColumns(#"Changed Type",{"Source.Name"}),
    #"Renamed To Transaction Type Column" = Table.RenameColumns(#"Removed Source Name Column",{{"Column2", "Transaction Type"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed To Transaction Type Column",{{"Date", type date}}),
    #"Replaced ((( with VIS Value" = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3NNQ3MlLSUQIiJ0cfRz9nVwgHiAwNDJRidaKVTOCKwjyDgaSHq6NPiAdcGVgNhBns4R8A04skA9GGkETV5BtTamBgZBYM4RnBNVqgWevpF+KjYGCKzQjnIFfHEM8wVwVnH2zSpqZQHxqZwiQMgD43wulzE1Ol2FgA", 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 in" = _t, #"Paid out" = _t, Balance = _t]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Replaced ((( with VIS Value",{{"Paid in", type number}, {"Paid out", type number}, {"Balance", type number}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type2","",null,Replacer.ReplaceValue,{"Date"}),
    #"Filled DownX" = Table.FillDown(#"Replaced Value",{"Date"}),
    #"Replaced ValueX" = Table.ReplaceValue(#"Filled DownX",null,0,Replacer.ReplaceValue,{"Paid in", "Paid out", "Balance"}),
    Custom1 = List.Accumulate(Table.ToRecords(#"Replaced ValueX"), {}, (a, n)=> 
            if n[Payment type and details] = "BALANCE" then {n} & a else 
            if n[Transaction Type] <> "" and n[Transaction Type] <> null then {n} & a else 
                let 
                    rLast = List.First(a),
                    transform = Record.TransformFields(rLast, {{"Payment type and details", each _ & " " & n[#"Payment type and details"]}, {"Paid in", each _ + n[#"Paid in"]}, {"Paid out", each _ + n[Paid out]}}),
                    out =  {transform} & List.Skip(a)
                in out ),
    Custom2 = Table.FromRecords(List.Reverse(Custom1), Value.Type(#"Filled DownX"))
in
    Custom2

Cheers,

John