Power Query - Combine, transform and track data

[Bulldog12]

Hello All,

As a reasonably seasoned excel user, im embarking on learning power query to try to achieve something which could quite well be impossible So hoping I can get some clarification from the real experts!

Situation: I have a CSV file provided to me every day detailing a list of accounts which have errors, 1 account per row. Each day some of the rows may remain the same (i.e. the account is continuing to error), some will have disappeared (i.e. the error has been fixed) and then there will be new accounts with errors.

Goal: I want to have a single file with the updated error accounts, and enable me to track what has been done so far (maybe with a status column and a notes column). So, lets say on Monday I work through the list and update the status for each error and put some notes to say what I have done. Then on Tuesday I get my new file which I want to combine with Mondays file, but only show unique values and retain my comments from the previous day.

So far I have figured out how to combine the 2 files (without and additional status/notes fields). And im really struggling to plan in my head how I should go about tracking the status/comments each day without losing it when I combine yesterdays file with todays file.

Hoping that makes some level of sense and I thank anybody who has the time to push me in the right direction.

 

[sandy666]

so you need to see only table with errors and fixed errors?

 

[Bulldog12]

Trying to figure out how to explain it in the best way

Monday: I have a set of data that I will work on (add status's and notes to each row)
Tuesday: I get a new file to combine into mondays table, retaining my status and notes for each row and appending new (unique) rows from Tuesdays file into Mondays file, and flagging rows in mondays file that are not in tuesdays file (i.e. have been fixed).

So what I am always left with are in-flight or new rows.

Hope that makes sense

 

[sandy666]

so merge 1csv with 2.csv via account, expand appropriate columns then remove duplicates

 

[Bulldog12]

Yeah now you say it like that it seems simple :D

But leaves me with a couple of questions:

1. How would you add the columns? If i simply add them to the table in 1.csv will they still be there after the merge? Or should I add them as custom columns in the power query?
2. removing duplicates: What is the best way to achieve this? Advance filter show unique records only? If so, how do I know it will keep the entry with my notes as opposed to the new entry from 2.csv which has no notes?

 

[sandy666]

1. How would you add the columns? If i simply add them to the table in 1.csv will they still be there after the merge? Or should I add them as custom columns in the power query?

I didn't say ADD column, I said Expand...

2. removing duplicates: What is the best way to achieve this? Advance filter show unique records only? If so, how do I know it will keep the entry with my notes as opposed to the new entry from 2.csv which has no notes?

on the PQ ribbon you should see Remove Rows - expand this and you'll see Remove Duplicates