Forum Discussion
Excel - Power Query - Bank Statements - Combining Data based on Date Values, Type, Descriptions
Sorry, added some data for testing and forgot to remove it :(.
Try tihs code, it should work now:
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"}),
#"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
Hi jbwtp
No worries at all.
Your code is now pulling through the source data from the PDFs in the folder, thank you very much, this is greatly appriecated.
I am not getting any sytax erros in your code data, however, after accepting the code there are errors showing in each table heading.
So x2 row errors are being reported:
It appears that the rows that originally had the 'Balance Brought Forward' and Balance Carried Forward' as separated data, as description and numberical values rows; one being at the top of the statement and the other at the bottom, are being combined with the data for the first and last transactions on each statement.
These would be in there own rows and show up as a filter option, but in this case as included in the other transactions, this is not showing due to them being merged.
This did work in the test table that you kindly pulled together, but not in this instance, so unsure if this can be separated as it I guess the code carries through to all rows and does not exlude the above specific rows. I hope there is a way to update this as each two transactions narratives and numberical values per statement which would have incorrectly named descriptions.
Can you advise if this can be changed?
Thank you so much again for your help, greatly appreciated.
Cheers
Max