Forum Discussion

Maxitco's avatar
Maxitco
Helper I
3 years ago

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

Hello All,    I am attempting to combine all my historic bank statements together by using Power Query.  I have already selected the source folder to bring 2 months of PDF bank statements to try ou...
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
Maxitco's avatar
Maxitco
Helper I
3 years ago

Thanks, 

 

I have tried your code, and this has corrected the descriptions and the balance brought forward and carried rows, but the numerical values are returing errors. 

 

 

jbwtp's avatar
jbwtp
Memorable Member
3 years ago

Try this:

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.ReplaceValue(#"Changed Type1",")))","VIS",Replacer.ReplaceText,{"Transaction Type"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced ((( with VIS Value",null,null,(x, y, z) as text => Text.Combine(List.RemoveItems(Text.ToList(Text.From(x)), {" "} & {"A" .. "z"})),{"Paid in", "Paid out", "Balance"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value1",{{"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 Text.StartsWith(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