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

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