Jan 02 2023 09:20 PM
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?
Jan 02 2023 10:32 PM
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/
Jan 03 2023 01:28 PM
Jan 02 2023 10:32 PM
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/