Data Flow - > Sheet Development Image: enter image description here
Partial Sheet image: enter image description here
I have been able to make two sets of self referencing sheets. One works and the other does not.
For the one that works: I have no formulas. It is a straight data entry that is added after the query has been created. all info is maintained and the indexing is maintained between the Original Query and the Expanded Columns (via the self-referencing query).
For the one that has failed, I have (#1) twelve columns one for each month of the year, (#2 ) an "Invoiced" column , which sums all of the charges that came in on that line item (Jan -> December) and (#3) a "Reconciled" column that should compare the test cost to the summation and 'doing stuff' based on the equality of the two cells.
How I made the sheets:
- create a Query based off of two tables
- add new columns to the query sheet and "expand" the table to include the new columns.
- Create "Reference Only" Self referencing sheet based off of the Expanded Query so any new data added to the Months column is not erased.
- Merge the Query and the Self Referencing Sheet.
Unfortunately, the formulas only work until the data is refreshed and they get overwritten(?).
Please help - I must be expanding the Query sheet in the wrong order or something?
Also:
I am interested in hearing about any Intermediate to Advanced Power Query classes you have taken.