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

[Maxitco]

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 out this method and have combined them together 

The following issues that I am having are as follows:

1. I can't seem to combine all the transaction rows which all full under the specific date. So the first line shows a date and any other transactions that show for the same period show as 'null'.   So the 'null' data in the 'Dates' column should match the first date for any new transaction detail for that date.

2. The bank statement has a 'Transaction type' column which is split between 'null', '(((' & 'DD', and this is shown on the first specific rows that have a new transaction associated with it. So if there is a 'null' value below a transaction type of 'DD' then this transaction description is linked with the first row.

3. The bank statement has a 'Payment type and details' column which lists the transaction information, however there are multiple transaction rows that are made under the same date and are linked to a specified 'Transaction Type' for each and I want to combine these togeter somehow and the additional information to bring together (prefer onto one line for each transaction type).

4. The 'Balance' column I don't really mind as much as I wish to them transpose the data into a Pivot table, but what you can see is that there is a numerical value that has a 'D' in it. 

5. I wish to remove all 'Balance Brought Forward, and Balance Carried Forward' rows and Columns as only interested in the 'Paid out' and 'Paid in' amounts. 

Power query is new to me, however I have spent a few hours learning what I can understand from videos, but just can't seem to implement to clean up this data.

I hope someone can be of assistance and help with the above. 

Looking forward to what options I can take to resolve this. 

Many thanks in advance.

[Poohkrd]

Hi, @Maxitco, can you show example of your data in xlsx/pbix file, and the result you want.

[Maxitco]

Hi @Poohkrd 

Thanks for reply. 

I am new to this forum and can't seem to upload the xlsx spreadsheet, thou I have uploaded an additional picture which is a snip of what I have prepared in a new sheet to assist you. 

I hope you can understand

[jbwtp]

Hi @Maxitco,

Please try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3NNQ3MlLSUQIiJ0cfRz9nVwgHiAwNDJRidaKVTOCKwjyDgaSHq6NPiAdcGVgNhBns4R8A04skA9GGkETV5BtTamBgZBYM4RnBNVqgWevpF+KjYGCKzQjnIFfHEM8wVwVnH2zSpqZQHxqZwiQMgD43wulzE1Ol2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Type = _t, Details = _t, In = _t, Out = _t, Balance = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Type", type text}, {"Details", type text}, {"In", type number}, {"Out", Int64.Type}, {"Balance", Int64.Type}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Date"}),
    #"Replaced Value" = Table.ReplaceValue(#"Filled Down",null,0,Replacer.ReplaceValue,{"In", "Out", "Balance"}),
    Custom1 = List.Accumulate(Table.ToRecords(#"Replaced Value"), {}, (a, n)=> 
            if n[Details] = "BALANCE" then {n} & a else 
            if n[Type] <> "" and n[Type] <> null then {n} & a else 
                let 
                rLast = List.First(a),
                transform = Record.TransformFields(rLast, {{"Details", each _ & " " & n[Details]}, {"In", each _ + n[In]}, {"Out", each _ + n[Out]}}),
                out =  {transform} & List.Skip(a)  //List.Skip(a) & {transform}
                in out ),
    Custom2 = Table.FromRecords(List.Reverse(Custom1), Value.Type(#"Changed Type"))
in
    Custom2

[Maxitco]

@jbwtp 

Wow, thank you very much, much appreicated. 

The 'Source' however has changed and does not look for the given folder that has the bank statements to pull the data from. 

So how do I update the source to seek the folder statements or amend the code that you have provided to each time I add a new statement I can click on update and pull in the new data?

Also the column titles will be listed as below, so I am unsure how this would also change the code you kindly provided.

Date

Transaction Type

Payment Type and details

Paid Out

Paid In

Balance

The below is a snip of the basic folder location and PDF bank statement to pull the data from.

C:\Users\HSBC Advance Account

So normally I would go into the 'Source' Applied Steps and edit the location to where the PDFs are stored, but this amendment only shows the table?

Hope there is a way to do this.

[jbwtp]

Hi @Maxitco,

1. How to incorporate the code into your existing codebase:

a. As a separate query: Change reference to #"Changed Type" in the line below to reference to the name of your existing query:

#"Filled Down" = Table.FillDown(#"Changed Type",{"Date"}),

 b. Add to your existing query: copy the query from the line above to the rest of the query and then paste into your query after the [so far] last step (do not forget to remove the existing "in" staement at the end of your existing query and add comma at the current last line in your query [this is syntax requirements]).

2. Change of column names:

 rename references to columns in my code: Type to Transaciton Type, Details to Payment Type and details and so on.

Hope this helps,

Kind regarfds,

John

[Maxitco]

Hello @jbwtp 

Thanks for your reply. 

I am really sorry to say that I don't follow your latest instructions in respect to the advance code you supplied. My terminology and language skill in Power Query is still at the beggining level and any adjustments that I have made have not been directly through the advance coding, only the 'Applied Steps'.

Can you kindly provide highlights with the areas that need to be updated within the code in question which need to be referenced, moved or changed according to your direction?

Are you able to use the 'Snipping tool' when posting to highlight specific areas where the information is and where it needs to go and reference the text lines that needs updating at all, deleting or moving up or down the code?

I know that, as highlighted below in yellow, that the column names can be changed and would be reflected in the code if updated in the Query Settings code or can be amended in the code directly. 

Just as an example, as per your first point I get a error message when updating the #"Filled Down" line code to #"Changed Type" 

Of course I would need to add the source of the data as below to the code to pull from the folder.

let
Source = Folder.Files("C:\Users\Documents\HSBC Excel Power Query - Accounts Download\HSBC Advance Account"),

Thanking you for your patience and advice in advance.

Kind regards,

Max 

[jbwtp]

Hi Max,

P.S. Sorry I realised that this is my code on the screenshot above. Re-wrting.

Please share the code that brought you to the step on the first screenshot in your original post.

I will intergate the remaining bits.

Cheers,

John

[Maxitco]

Hi John,

No problem at all.

Please see below the screenshot along with the code, and this is the stage that I managed to get to at the start. 

Date

Transaction Type

Payment Type and details

Paid Out

Paid In

Balance

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"})
in
#"Replaced ((( with VIS Value"

I hope the above is ok. 

Thanks

Max

[jbwtp]

Hi @Maxitco,

Please try if the below works:

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.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3NNQ3MlLSUQIiJ0cfRz9nVwgHiAwNDJRidaKVTOCKwjyDgaSHq6NPiAdcGVgNhBns4R8A04skA9GGkETV5BtTamBgZBYM4RnBNVqgWevpF+KjYGCKzQjnIFfHEM8wVwVnH2zSpqZQHxqZwiQMgD43wulzE1Ol2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Transaction Type" = _t, #"Payment type and details" = _t, #"Paid in" = _t, #"Paid out" = _t, Balance = _t]),
    #"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

Cheers,

John

[Maxitco]

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]

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

[Maxitco]

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

[jbwtp]

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 => Text.Combine(List.RemoveItems(Text.ToList(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

[Maxitco]

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]

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

[Maxitco]

Hi @jbwtp 

Thank you, 

Some of the numerical values have returned, however there are errors that still show. Not sure where this is originating from. 

Please advise

Thank you

Max

[jbwtp]

in theory, you could solve it by suppressing errors afte the #"Replaced Value1" step. This is where the value was = null, so you can replace Errors with nulls.

The code below addreses the problem anyway. Try if it works.

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
    Custom2

[Maxitco]

Hi @jbwtp, 

Thank you.

I can't seem to remove the error and replace with null value as it asks to add a date value. 

There are also numerous amount values that are missing from the statement as highlighted in yellow columns, which are showing as errors, and so these values are not updating through to the balance column.

Below is what the data looks like in the 'Replaced ValueX' query step, before the code is updated to reflect the above. So not sure why the other numerical values are missing. 

Can you kindly advise

Thank you so much for your help and patience in this, greatly appreciated. 

Max

[jbwtp]

Have you tried the code that I provided in the previous reply? Is this not solving the problem?

[Maxitco]

Hi @jbwtp 

Yes I have tried your code and it does not work sadly.  As shown in my post above, there are numerical values that are missing from the imported statements. It is not just a case of some of the fields being null and showing as a error, it is that the highlighted values in yellow are missing and showing as errors. So not sure what the difference is to why the the highlighted amounts are showing as error messages and why are some values coming through correctly from the source statement.