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

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

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

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

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
    Custom2

Thanks,

John

Hi Max,

Let's start with this one. Sorry, for some reason the code did not copy correctly, there should be "0" rather than 0 in the Replace Value step, which you have deleted.

This is an important step as the data is not perfect and needs cleansing (it fixes 136 D problem amongst others). Leave it in the code, just change to:

#"Replaced ValueX" = Table.ReplaceValue(#"Filled DownX",null,  "0"   ,Replacer.ReplaceValue,{"Paid in", "Paid out", "Balance"}),

Hi Max,

Regarding the dates: can you pleaes check when the error first time manifiest itself in the code?

There is nothing obvious that I can see, so I suspect that this can be in the 

"Transform File"

Query/function.

How the #"Expanded Table Column1" output looks like?

Cheers,

John

Hi Max,

I think we need to break it into digestable pieces.

Could you please delete everything past the VIS step. only leaving these ones:

Make sure there is no errors in the output and then copy and past the data in the Excel file (click in the table sign in the top-left corner):

Edit the data in Excel to remove the sensitive info (in the payment type and details column), but ideally leave the strucutre (e.g. if you have something like Mr John Doe, change it to Mr Don Duck, but leave the text, so I could see what to expect in the column).

Then copy and past the table from Excel to the forum (as text and not as picture, please). I will see what needs to be done to transform it into the desired output.

Please note, that I've started an urgent project that we need to complete before Xmas, so from this week my responses can be slow (or may be I will not be able to reply until Jan), sorry.

Cheers,

John

Hi Max,

This is the code for the above. Just add it as a separate query (New Source->Blank Query)

and replace the content of the new query with the code below, then replace the Source ste pcontent to a reference to your original query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zVbbbtswDP0VI8+pKlH3Pk1x3MZD4gS2s6Ho+gfF3vr/k2Q7YRw7S5wO2ItcpCQPKR4e6u1tRtkjY49AmZrNZ78/Pz78Z+HWrkizZFFu9y+rOnnelj9duTwatB9tiJy9z30QgYL8yCt/rlxepvv6zCd+gs/pz+tt6up8W/g/mR23bmLvyqyqkmq13U0Ib4lFAMISyZoa9FkNeVH/+qQU9DrxpxTCyGsB0zJzdZKur7U3lD4YkA9KGRHSkkTz43850aZJ0qIkl6EjdYBgJHq11oxYEa2Z7lv7irKyiFfhoiOXRBrkyjlRIlk27ga5p2U4tpudQ9ZKaULFQFURbJm/5HWE4YCc5pIyImg0B9bPcLdOr72znXvdZEWgmKQh7XGHGDltKKxAh5Yf71ZTAg2NYYDGmVvXqyks9l1AbOHSEN6AyDOQrEj8pNXZFBiMIXiHgRu/CFNSuE2WhOPGyw3pMT/nArGRKUGMbvjo+wcj6uHvu8yz5ah6XBnmbyKEw2gU5u4RrvaLKs137UV/wRQzZgkThzmG8zkGOBEnqwhrmOmN+5VNlD9x0krLiIYGQSGEoWEHytthhzkDQ7Rq/M4qOdMYgQVmbg3tfIH2fcckw4oDILC+UyMZQQOExE7C3z9vxB1436lVA2De64JyuHoTjaubNSD5xuAJWLw5nBV4ZWj5CuarutptM99OO+BwV2x+mj54qVXd1NKoMnrC8F8b6K/jfxKIo0AH3ZsgeYqiohUCkAjgLm7op0hWzA1FjzhqECeZDsQQkLRHIIuARoVomE//zfNIgjg8eQDVczvtX/ZeuqrsdRZfURZDQNjnrVLeA4GXN+EnEIxI1T3cvgyCGQxBCTfd467X+Iu63Ue6oNbjEpp8dwXrB72BwxJxGOyxFPtvbguMJdAtqiPC4EtYU0WoGgCpn4O5VEbKREkLXPa3zWhidemK6jkr4/RiQbJWElDdYuu1cXgbDvav3YFcWsIv7MBbpZ0DEX7Bvf8B", 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 out" = _t, #"Paid in" = _t, Balance = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Transaction Type", type text}, {"Payment type and details", type text}, {"Paid out", type number}, {"Paid in", type number}, {"Balance", type text}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Date"}),
    #"Fix Numbers" = Table.ReplaceValue(#"Filled Down",null,0,(x, y, z) as number=> if x = null or x = "null" then 0 else Number.From(Text.Remove(Text.From(x), {"A".."Z"} & {"a" .. "z"} & {" "})) ,{"Paid out", "Paid in", "Balance"}),
    #"Fix Types" = Table.ReplaceValue(#"Fix Numbers","null","",Replacer.ReplaceValue,{"Transaction Type"}),
    #"Added Conditional Column" = Table.AddColumn(#"Fix Types", "Filter", each if [Paid out] + [Paid in] + [Balance] <> 0 then true else false, type logical),
    Process = List.Skip(List.Accumulate(Table.ToRecords(#"Added Conditional Column"), {[Filter = false, Count = 0]}, (a, n) => a & {Record.AddField(n, "Count", List.Last(a)[Count] + (if List.Last(a)[Filter] then 1 else 0))})),
    Format = Table.FromRecords(Process, Value.Type(Table.AddColumn(#"Added Conditional Column", "Count", each null, type number))),
    #"Grouped Rows" = Table.Group(Format, {"Count"}, {{"Date", each List.Last([Date]), type nullable date}, {"Transaction Type", each List.First([Date]), type nullable date}, {"Payment type and details", each Text.Combine([Payment type and details], " "), type nullable text}, {"Paid out", each List.Sum([Paid out]), type number}, {"Paid in", each List.Sum([Paid in]), type number}, {"Balance", each List.Sum([Balance]), type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Count"})
in
    #"Removed Columns"

It works on my side, hopefully, will work on your side too.

I am not sure that I can help to resolve the issue with importing PDF's second page from my side, this may require some experimenting. If the date would not be missing competely, I think I could do something to format it right, but if this does not come through to PQ, I do not know even what to start with :(. Maybe try playing with the import function paramaters?

Cheers,

John

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

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

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. 

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

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

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

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.   

They disappear because adding to Error always produce Error. What the error on the  'Replaced ValueX' step? What is the earliest step where this error appears first?

Cheers,

John

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

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

Hi jbwtp 

In addition to my above post, can I ask for clarification as below please?

1. The bank has a weird way of now displaying a date for each transaction so when it comes to pulling the data from the PDF statements and there is a overflow to a second page the data does not pull through due to the missing date as highlighted below. Only the first page seems to come through.

2. Also the transaction description seems to be acting funny with entering the fields as text as there is data to pull through however the field is unable to convert from null to type as shown in the error below.

And the same date keeps repeating (22.10.22) but there are other dates as shown in the below statement.

I hope you can shed some light on this.  

Thanks

Max

Hi jbwtp 

Thank you John. 

I have listed the issues under points for ease. 

Issue 1. is the statement figure issue showing as errors and not pulling through.

Issue 2. is the additional pdf data from other statements not pulling through which could be the a source code issue and I have added information to assist you.

Issue 1.

1. I have added the deleted line below back into the code. 

#"Replaced ValueX" = Table.ReplaceValue(#"Filled DownX",null,  "0"   ,Replacer.ReplaceValue,{"Paid in", "Paid out", "Balance"}),

Sadly this does not bring back the missing numerical values in the 'Paid out', 'Paid in' and 'Balance' columns.

The 'Replaced Value1' step is where all the transaction figures shows in each statement column, but the figures in the 'Balance' column disappears from the 'Changed Type2' Step onwards, but maintains the figures in the 'Paid out' and 'Paid in' columns. I have listed each step with the code for reference.

Figure 1. All figures presented

#"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"}),

Figure 2. Balance column figures missing

#"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value1",{{"Paid in", type number}, {"Paid out", type number}, {"Balance", type number}}),

Figure 3. Missing figures in all figure columns

Custom2 = Table.FromRecords(List.Reverse(Custom1), Value.Type(#"Filled DownX"))

Issue 2. 

---

jbwtp wrote:

Hi Max,

 

Regarding the dates: can you pleaes check when the error first time manifiest itself in the code?

There is nothing obvious that I can see, so I suspect that this can be in the 

"Transform File"

Query/function.

How the #"Expanded Table Column1" output looks like?

 

Cheers,

John

---

I have added a new statement into the same source data folder and filtered only for this statement period. You can see that the next page figures are not pulling through and the only data that is pulling through is only up to 23 Jan 2017; 27-29 Jan 2017 is not pulling through as I think that there is no date listed for Power Query to look up and this is why it is missing. 

Is there a code text that can be incorporated to search for the data and populate the transactions with the existing date from the previous statement page and also add through on additional pages?

For the issue below, when I added this statement for October, the step jumps straight to the error message, but if I look back on the steps I can see the below data showing like this on the 'Replaced ValueX' step.

I have searched each step all the way back to the source and it still shows 'null' in the Description line and does not pull in any data. 

I have started a new spreadsheet and imported just the PDF folder to see what is happening at source and can see that the second statement page is not pulling in the data and also some of the data is missing from page 1. So only a captive area is being seletected

Sorry for the very long post.  I wanted to be sure that I had covered as much as possible to assist you in helping me. 

Hope this helps. 

Thank you

Max

Hi jbwtp 

Thank you and I understand. Thanks for your help.

Please see below the raw data from 3 statements which I have copied as text.  I have before deleted the other steps and left what you have advised. 

Note that there are more transactions after 23/01/2017 which go onto a second page in the statement, however as some of the transaction form part of the 23/01/2017 and there is no date to bring them into Power Query, they are jus missing. So the import is not grabbing this additional data from the PDF. Not sure why this is the case.

I have kept the format for the description and details and removed personal details and replaced with fictitious information. The cell formats remain the same.

Note, as you may remember, that there are additional description lines which can be merged into one line and this can be seen by the transaction type reference as there may be more than one transaction for each date; and also that additional transactions do not have a date associated with it and show as null.

Cheers 

Max

Hi jbwtp 

Many thanks for your message.

I have added the new source code and this has created a new name as "Query1" , and the result is shown below. 

Question: I need some further clarification on how to implement the step as per your instruction below in Red to update the transaction descriptions to the source folder data:

and replace the content of the new query with the code below, then replace the Source ste pcontent to a reference to your original query:

Regarding the secon pages not pulling through, I may have to just add these in manually when reviewing the data.

Many thanks for your assistance in this as its greatly appreciated.

Cheers Max

Hi Maxitco,

Sorry for the delay, was busy with another deadline.

In your case (as I can see on the screenshot) the first step would look like:

Source = #"HSBC Advance Account" // which is the name of your existing query, which returns the list of transactions from the statement

cheers,

John