Forum Discussion
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...
Hi jbwtp
The Step 'Replaced ValueX' is replacing Values from 'null' with '0'
The Step named 'Replaced Value1' is where the 'Errors' start to show.
At the step of 'Replaced Value1' all the data shows no 'Errors', shown below, but the all the amounts are shown in the 'Paid Out', 'Paid In' and 'Balance' columns.
The amounts highlighted in yellow in the previous post show the figures that have disappeared in the 'Paid Out', 'Paid In' and 'Balance' columns have been removed from Step 'Custom2' .
I hope this helps clarify your questions.
Cheers
Max
Thank you, Max.
The problem, I think, in the replacer which is expected to return a text value, but returns 0 as a number. Could you please try this code:
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 => if x = null then 0 else 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
Custom2Thanks,
John