Forum Discussion

Mouseman85's avatar
Mouseman85
Icon for Helper I rankHelper I
2 years ago
Solved

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

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.

General Comment
Need Help
Tutorial Requests

7 Replies

    • Mouseman85's avatar
      Mouseman85
      Icon for Helper I rankHelper I
      2 years ago

      Hi 

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

       

      Forecasted Table:

      Record IDDeal NameValueSource
      15324788626Project 1100Forecasted.xlsx
      15234427988Project 2300Forecasted.xlsx
      15154789485Project 3500Forecasted.xlsx
      15154789193Project 4100Forecasted.xlsx
      15337558845Project 560Forecasted.xlsx
      14891035915Project 670Forecasted.xlsx

       

      ProcimForecasted:

      Record IDDeal NameValueSource
      1Project 102100ProcimForecasted.xlsx
      2Project 202200ProcimForecasted.xlsx
      3Project 203400ProcimForecasted.xlsx
      4Project 204100ProcimForecasted.xlsx
      15337558845ProjectNo- Project 5150ProcimForecasted.xlsx
      14891035915ProkectNo - Project 6270ProcimForecasted.xlsx
      6Project 205340ProcimForecasted.xlsx
      7Project 206500ProcimForecasted.xlsx
      8Project 307100ProcimForecasted.xlsx

       

      Appended PRocimForecasted to Forecasted table result:

      Record IDDeal NameValueSource 
      15324788626Project 1100Forecasted.xlsx 
      15234427988Project 2300Forecasted.xlsx 
      15154789485Project 3500Forecasted.xlsx 
      15154789193Project 4100Forecasted.xlsx 
      15337558845Project 560Forecasted.xlsxREMOVE THESE AS THEY EXISIT IN PROCIMFORECASTED.XLSX
      14891035915Project 670Forecasted.xlsxREMOVE THESE AS THEY EXISIT IN PROCIMFORECASTED.XLSX
      1Project 102100ProcimForecasted.xlsx 
      2Project 202200ProcimForecasted.xlsx 
      3Project 203400ProcimForecasted.xlsx 
      4Project 204100ProcimForecasted.xlsx 
      15337558845ProjectNo- Project 5150ProcimForecasted.xlsx KEEP THE UPDATED VALUES
      14891035915ProkectNo - Project 6270ProcimForecasted.xlsx KEEP THE UPDATED VALUES
      6Project 205340ProcimForecasted.xlsx 
      7Project 206500ProcimForecasted.xlsx 
      8Project 307100ProcimForecasted.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.

    • Mouseman85's avatar
      Mouseman85
      Icon for Helper I rankHelper I
      2 years ago

      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

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    2 years ago

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

     

    • Mouseman85's avatar
      Mouseman85
      Icon for Helper I rankHelper I
      2 years ago

      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 IDDeal NameValueSource 
      15324788626Project1100Forecasted.xlsx 
      15234427988Project2300Forecasted.xlsx 
      15154789485Project3500Forecasted.xlsx 
      15154789193Project4100Forecasted.xlsx 
      15337558845Project560Forecasted.xlsxREMOVE THESE AS THEY EXISIT IN PROCIMFORECASTED.XLSX
      14891035915Project670Forecasted.xlsxREMOVE THESE AS THEY EXISIT IN PROCIMFORECASTED.XLSX
      1Project102100ProcimForecasted.xlsx 
      2Project202200ProcimForecasted.xlsx 
      3Project203400ProcimForecasted.xlsx 
      4Project204100ProcimForecasted.xlsx 
      15337558845ProjectNo- Project5150ProcimForecasted.xlsx KEEP THE UPDATED VALUES
      14891035915ProkectNo - Project6270ProcimForecasted.xlsx KEEP THE UPDATED VALUES
      6Project205340ProcimForecasted.xlsx 
      7Project206500ProcimForecasted.xlsx 
      8Project 307100ProcimForecasted.xlsx 
  • Mouseman85's avatar
    Mouseman85
    Icon for Helper I rankHelper I
    2 years ago

    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