Table Equations adjacent to Power Query result

[Paul_Christie]

Ken,

This is sort of related to my previous query about things not refreshing. Now I've resolved that the queries that are dependant on the 'location' chosen all refresh ok and the formula columns adjacent to those PQ Tables all copy down ok. A result.

Now the department table and dropdown doesn't populate until the Location query has established what departments are valid for the chosen location so you end up with a department table with an empty first row. That means there are no equations where in the adjacent columns there previously were.

On a previous project where i used an empty table with data validation and formulas in place on row 2 I was able to populate a left hand set of columns with data from the database starting in row 2 and the formula columns would self populate.

I'm wondering if I can do a similar thing now and how I stop Power Query deleting the formulas.

Paul

 

[Paul_Christie]

Ken

A bit more information. When the Location and associated tables are chosen and refreshed the Department table reverts to a single row rable with the formulas in the first data row. It's when the department query is refreshed that the equations get zapped.

Paul

 

[Paul_Christie]

Following my normal pattern of answering my own questions the answer to the above is here
http://www.excelguru.ca/blog/2016/08/23/fix-excel-formulas-dont-update-in-power-query-tables/
I'd already done this on the primary table and forgotten to do it on the dependent table.
This is a lesson in stopping work when you are tired, have a rest, and see miracles occur when you come back to the problem.