Append two tables remove duplicate value from original table to keep appended table value

[Mouseman85]

Hi,

I have two tables, Forecasted and ProcimForcast. I need to append the values from ProcimForecast to the Forecasted Table. Each table has a unique field called Record ID. In the ProcimForecast table that Record ID can change to be a matching value of the same record in the Forecasted table, so when i append them i get two records with the same Record ID, but slightly different name (when the record is created in our PRocim system it is given a project code so the name would be ProjectCode_NAME, but the item will also exist in the Forecasted table as this comes from deals in our CRM system before they have been pushed into our costing system to pull in the actual values. 

I need a way in Power Query to some how look up records in the Procim Forecast table that match a Record ID in the Forecasted table and import those and anything that does match, but then remove the Record IDs from the Forecasted table that match so I don't get a duplicate.

I can't do this on a custom column after transforming the data as I am using the Record ID field as a Key field between another table called Forecasted Pivot which is using a many-to-one relationship so the when loading the data in it fails to load.

[Ahmedx · Accepted solution]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

[Mouseman85]

Hi,

I tohught a screen grab might work easier, in the below, you can see the duplicates what i want to do is when i bring them all into one table that if deal name/record ID exists in the sources 3436ProcimActive or 3430ProcimPitch then remove the HubSpot source so that that it uses the values from the other sources and the not the initial HubSpot values

[danextian]

Is this how your second table should look? Two rows after 4 seem to be off.

[Mouseman85]

Hi sorry,

It should be record ID as the number 1 and then the long number then Deal Name should be Project or ProjectNumber-Name102,103 etc then the value should just be the single numbers : 

Record ID	Deal Name	Value	Source
15324788626	Project1	100	Forecasted.xlsx
15234427988	Project2	300	Forecasted.xlsx
15154789485	Project3	500	Forecasted.xlsx
15154789193	Project4	100	Forecasted.xlsx
15337558845	Project5	60	Forecasted.xlsx	REMOVE THESE AS THEY EXISIT IN PROCIMFORECASTED.XLSX
14891035915	Project6	70	Forecasted.xlsx	REMOVE THESE AS THEY EXISIT IN PROCIMFORECASTED.XLSX
1	Project102	100	ProcimForecasted.xlsx
2	Project202	200	ProcimForecasted.xlsx
3	Project203	400	ProcimForecasted.xlsx
4	Project204	100	ProcimForecasted.xlsx
15337558845	ProjectNo- Project5	150	ProcimForecasted.xlsx	KEEP THE UPDATED VALUES
14891035915	ProkectNo - Project6	270	ProcimForecasted.xlsx	KEEP THE UPDATED VALUES
6	Project205	340	ProcimForecasted.xlsx
7	Project206	500	ProcimForecasted.xlsx
8	Project 307	100	ProcimForecasted.xlsx

[danextian]

Hi @Mouseman85 ,

Please post a sample data (not an image) and your expected result.

[Mouseman85]

Forecasted Table:

Record ID	Deal Name	Value	Source
15324788626	Project 1	100	Forecasted.xlsx
15234427988	Project 2	300	Forecasted.xlsx
15154789485	Project 3	500	Forecasted.xlsx
15154789193	Project 4	100	Forecasted.xlsx
15337558845	Project 5	60	Forecasted.xlsx
14891035915	Project 6	70	Forecasted.xlsx

 ProcimForecasted Table

Record ID	Deal Name	Value	Source
1	Project 102	100	ProcimForecasted.xlsx
2	Project 202	200	ProcimForecasted.xlsx
3	Project 203	400	ProcimForecasted.xlsx
4	Project 204	100	ProcimForecasted.xlsx
15337558845	ProjectNo- Project 5	150	ProcimForecasted.xlsx
14891035915	ProkectNo - Project 6	270	ProcimForecasted.xlsx
6	Project 205	340	ProcimForecasted.xlsx
7	Project 206	500	ProcimForecasted.xlsx
8	Project 307	100	ProcimForecasted.xlsx

 Expected Results:

Record ID	Deal Name	Value	Source	
15324788626	Project 1	100	Forecasted.xlsx	
15234427988	Project 2	300	Forecasted.xlsx	
15154789485	Project 3	500	Forecasted.xlsx	
15154789193	Project 4	100	Forecasted.xlsx	
15337558845	Project 5	60	Forecasted.xlsx	REMOVE THESE AS THE EXISIT IN PROCIMFORECASTED.XLSX
14891035915	Project 6	70	Forecasted.xlsx	REMOVE THESE AS THE EXISIT IN PROCIMFORECASTED.XLSX
1	Project 102	100	ProcimForecasted.xlsx	
2	Project 202	200	ProcimForecasted.xlsx	
3	Project 203	400	ProcimForecasted.xlsx	
4	Project 204	100	ProcimForecasted.xlsx	
15337558845	ProjectNo- Project 5	150	ProcimForecasted.xlsx	
14891035915	ProkectNo - Project 6	270	ProcimForecasted.xlsx	
6	Project 205	340	ProcimForecasted.xlsx	
7	Project 206	500	ProcimForecasted.xlsx	
8	Project 307	100	ProcimForecasted.xlsx	

I've had to add it in as code snipts as when i used the table it won't post the message getting an invalid HTML error

[Mouseman85]

Hi 

Not sure if the below helps but this is a rough sample:

Forecasted Table:

Record ID	Deal Name	Value	Source
15324788626	Project 1	100	Forecasted.xlsx
15234427988	Project 2	300	Forecasted.xlsx
15154789485	Project 3	500	Forecasted.xlsx
15154789193	Project 4	100	Forecasted.xlsx
15337558845	Project 5	60	Forecasted.xlsx
14891035915	Project 6	70	Forecasted.xlsx

ProcimForecasted:

Record ID	Deal Name	Value	Source
1	Project 102	100	ProcimForecasted.xlsx
2	Project 202	200	ProcimForecasted.xlsx
3	Project 203	400	ProcimForecasted.xlsx
4	Project 204	100	ProcimForecasted.xlsx
15337558845	ProjectNo- Project 5	150	ProcimForecasted.xlsx
14891035915	ProkectNo - Project 6	270	ProcimForecasted.xlsx
6	Project 205	340	ProcimForecasted.xlsx
7	Project 206	500	ProcimForecasted.xlsx
8	Project 307	100	ProcimForecasted.xlsx

Appended PRocimForecasted to Forecasted table result:

Record ID	Deal Name	Value	Source
15324788626	Project 1	100	Forecasted.xlsx
15234427988	Project 2	300	Forecasted.xlsx
15154789485	Project 3	500	Forecasted.xlsx
15154789193	Project 4	100	Forecasted.xlsx
15337558845	Project 5	60	Forecasted.xlsx	REMOVE THESE AS THEY EXISIT IN PROCIMFORECASTED.XLSX
14891035915	Project 6	70	Forecasted.xlsx	REMOVE THESE AS THEY EXISIT IN PROCIMFORECASTED.XLSX
1	Project 102	100	ProcimForecasted.xlsx
2	Project 202	200	ProcimForecasted.xlsx
3	Project 203	400	ProcimForecasted.xlsx
4	Project 204	100	ProcimForecasted.xlsx
15337558845	ProjectNo- Project 5	150	ProcimForecasted.xlsx	KEEP THE UPDATED VALUES
14891035915	ProkectNo - Project 6	270	ProcimForecasted.xlsx	KEEP THE UPDATED VALUES
6	Project 205	340	ProcimForecasted.xlsx
7	Project 206	500	ProcimForecasted.xlsx
8	Project 307	100	ProcimForecasted.xlsx

But as stated above i need to the removal as a Power Query Custom column or other means as i can't create a new column when the data imports as it won't allow me to have two REcord IDS in the ForecastedResults table that match as that is being used as a link to a pivoted version of that table.