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

Hi jbwtp 

 

Thank you very much for the revised code:

 

The Code works, with no errors reported, however the source data is overwritten, Meaning that the imported PDF documents data from the source data folder is removed and just the data shown below is visable; it is not updated and I believe is based on the original manual table that was built and not the source folder, provided later in my post:

 

So I undertake the following steps:

1. Open a new spreadsheet

2. Get Data, From File, From Folder, Select Folder

3. Combine and transform Data

4. 'Table002 (Page 1)' only concerned data set is selected. Other table data is irrelevant.

5. The Query Settings Applied Steps are shown below:

 

6. This is the Raw data code which has not edited:

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}})
in
#"Changed Type"

 

7. If updated to the code you kindly provided only the below rows are shown. So by updating the source code hasn't brought through the source data from the folder, just the table I think the original table you kindly constructed in order to amend the code to merge the data fields was used.

 

Can you kindly advise what can be done please?

 

Thanks in advance

Cheers 

Max

 

jbwtp's avatar
jbwtp
Memorable Member
3 years ago

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