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

Hi jbwtp 

Thank you. 

I have entered your updated code, and at the start it did not solve the issue of the missing data, however as you suggested about about the replacer value being the issue, I removed this step completly from the code and it has brought back the missing numerical values. I have shown a image after removing #"Replaced Value1"

Below is the issue step:

#"Replaced Value1" = Table.ReplaceValue(#"Replaced ((( with VIS Value",null,null,(x, y, z) as text => if x = null then 0 else Text.Combine(List.RemoveItems(Text.ToList(Text.From(x)), {" "} & {"A" .. "z"})),{"Paid in", "Paid out", "Balance"}),.

And below is what the steps looked like prior to deleting this step.

This is what the code looks like after deletion of #"Replaced Value1" Step.

let
Source = Folder.Files("C:\Users\Max 2.0\Documents\MAX\BANK\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"}),
#"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 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

And when its imported into Excel.  There is 1 Error that remains which is in relation to the balance value where a letter is included '133.64 D' which brings through a blank field.  Not sure what could be updated in the code to allow this should ever a similar issue come around again when the balance is overdrawn.

I have now added more statements into the source folder and at present all seem to be pulling in correctly and into excel with your updated code.  

My next steps is to use a Pivot Table to consolidate all transactions together.

I like to thank you John, very much for your time and patience in helping me with this issue. I have learned a lot, as a beginner and is greatly appreciated.

Max