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.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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
 
Upvote 0
so merge 1csv with 2.csv via account, expand appropriate columns then remove duplicates
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top