Power Query - Combine, transform and track data

Bulldog12

New Member
Joined
Nov 15, 2019
Messages
3
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

Well-known Member
Joined
Oct 24, 2015
Messages
3,231
so you need to see only table with errors and fixed errors?
 

Bulldog12

New Member
Joined
Nov 15, 2019
Messages
3
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

Well-known Member
Joined
Oct 24, 2015
Messages
3,231
so merge 1csv with 2.csv via account, expand appropriate columns then remove duplicates
 

Bulldog12

New Member
Joined
Nov 15, 2019
Messages
3
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

Well-known Member
Joined
Oct 24, 2015
Messages
3,231
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
 

Forum statistics

Threads
1,077,895
Messages
5,337,054
Members
399,120
Latest member
Sravankumar

Some videos you may like

This Week's Hot Topics

Top