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.
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 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
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
Helpful resources
Power BI Monthly Update - August 2025
Check out the August 2025 Power BI update to learn about new features.
Fabric Community Update - August 2025
Find out what's new and trending in the Fabric community.
