Power Query Scrubbing- Business Case

[Anonymous]

I have a variety of data sources, that are all received form an emailed excel file (unfortunately I cannot connect driectly to database).

I have no problem joining them together and using power query.

however- one of the files essentially lists all the new construction projects. Part of our scrubbing (done in excel) is to use the Store No + Project ID to make a "key" column, and compare it to the new project list to see which projects are new that need to be added. Then projects that have fallen off of the current project list are moved to a different tab. 

I'm wondering if there is a way to do this within power query in Power BI. It would really save a lot of steps to automate this and be able to see the steps. 

I've only been in powerbi for about 5 months so still a newbie and not familiar with the full scrubbing capabilities.

[v-danhe-msft]

Hi @Anonymous,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[Anonymous]

Hi @v-danhe-msft

yes, that would solve most of the issues, I think in order to track how many projects were removed (from the new vs old) I would need to just keep track of how many total rows in both sheets, and subtract from each other. 

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

From your description, could you mean to add new data to the old table? If so, you could append your data list and remove the duplicate columns:
Sample data:

Table A

Table B

Append as new:

Remove the duplicate columns and you could get the result:

Regards,

Daniel He