Recognising Previously Imported Data - Bank Statements Project

[Ave663]

Hi All,

I am going through my personal bank statements project... see below for my overview process:

1. Import bank statement as a CSV into Power Query (upto date A)
2. In Power Query perform mapping and data organisation into the correct format
3. Output to Excel sheet in a table - This is illustrated in screenshot Step 1.
4. In the remaining columns of the table, add in some categories with a reconciled statement and some conditional formatting - This is illustrated in screenshot Step 2.
5. Now, Import next bank statement repeating steps 1-4 again...

However... I have hit a problem... this all works beautifully until the new statement contains some data left over from previous "Date A" (in step 1.) ... i.e. there are still some transactions from Date A that appear in both the original and the new bank statements

It then reorders the Excel sheet slightly such that the transactions on Date A have now moved around a bit (and as such, got out of step with those categories that have been added into the Excel sheet after the first import - grumble)... they are all registered on the same "time" in the bank statements so its not as if that can be used in the Power Query "sort process" in step 2. either...

It feels like i need to somehow pass back in the process somewhere, that data set A has already been imported so dumping data set B (new stuff) at the end regardless of date... to be sorted again properly...!

I think my key aim is: keeping the added categories data linked to the imported bank data once i have updated it in the worksheet...

Does anyone have any thoughts on how this can be done...? by this way or another...

Very much appreciated all thoughts...

Ian

 

[anvg]

Hi
I think my key aim is: keeping the added categories data linked to the imported bank data once i have updated it in the worksheet...
I think it sounds like Self Referencing Tables in Power Query
Regards,

 

[Ave663]

Hey...thanks for this... it looks like exactly (in principle) what i need... being new to this it references a load of stuff i have no idea how to do, although thats the fun part eh?!

Thank you very much for this response... Now i just need to more information on how to do more detailed stuff in Power Query.. I have just been messing about with this myself up till now...

Ian

 

[Ave663]

All sorted ... thank you Works a treat!!

 

[pssofiya]

I just wanted to add here that accounting and banking are two different terms which people get so confused with that they use the terms to mean the same.