SOLVED

Keep the added column intact when updating the new data

Copper Contributor

Hi,

 

This is my first time posting on the community. Please let me know if any additional information is needed.

 

Background:

I have different tables (product, sales, product details) in excel and updated new data in the work book every week for reporting.
I added an additional field called Revenue Adjustment into the original/raw Revenue Sales Table - to manipulate/make changes to the Revenue field. Then, using power query to join these together so I can have a master table for reporting.

Problem: 

Because the added Revenue Adjustment field is manually being entered by me. It does not stay intact or align with the data as I update the new raw data.

 

Is there a way to work around so excel will automatically keep the old adjustment I made and that this revenue adjustment field stay aligned with the new data? 

 

2 Replies
best response confirmed by lyly1203 (Copper Contributor)
Solution

@lyly1203 Google for "self referencing tables power query" and you'll find many sites that deal with this. The main principle is that you connect to a data source (call it source 1). Do the necessary transformations and load it back to Excel. Let's say this table is then called PQ_output, Add your own column(s) expanding that table. Now, change the original query and add a second source (call it source 2) that connects to the PQ_output table that now includes the extra columns and merge it with source 1 within the same query.

 

A simple example is demonstrated in the link below.

https://exceleratorbi.com.au/self-referencing-tables-power-query/ 

This helps a lot. Thank you so much!
1 best response

Accepted Solutions
best response confirmed by lyly1203 (Copper Contributor)
Solution

@lyly1203 Google for "self referencing tables power query" and you'll find many sites that deal with this. The main principle is that you connect to a data source (call it source 1). Do the necessary transformations and load it back to Excel. Let's say this table is then called PQ_output, Add your own column(s) expanding that table. Now, change the original query and add a second source (call it source 2) that connects to the PQ_output table that now includes the extra columns and merge it with source 1 within the same query.

 

A simple example is demonstrated in the link below.

https://exceleratorbi.com.au/self-referencing-tables-power-query/ 

View solution in original post