Self referencing query

Sorry, I burnt myself out looking for an answer before posting. To make a long story short, I created a query that appends 2 tables and a self referencing table. This works great. From that query, I added another column in excel, then created a new self referencing query. Again, works perfect. Problem I'm having, is the column I added in Excel only remembers values after a refresh, not my excel formula that generates that value. I intended on the column being a calculated column, but I want to use excel to write the formula, I don't want to do it in Power Query.

A little insight into what my queries do: I'm an estimator for a construction company. The three tables I appended together are basically for generic prices of materials. These prices remain unchanged for 6 months at a time... except for just a couple with each bid I put out. My queries filter all my materials into a list of just the materials I'm using in my current bid. Everything works perfect up to the refresh. My excel formula disappears and leaves just the values. This is a major problem for me, because I foresee adding many more columns where I can overwrite the formulas, but not lose the rest of the columns formulas on a refresh.

If I've explained my problem well enough, is it possible the have a self referencing query remember formulas, not just values?

Thank you so much for your time.

I don't know if it will help in your case, but a quick read of this article might be emlightening: https://www.linkedin.com/pulse/dont-get-caught-power-query-trap-wyn-hopkins/

AliGW said:

I don't know if it will help in your case, but a quick read of this article might be emlightening: https://www.linkedin.com/pulse/dont-get-caught-power-query-trap-wyn-hopkins/

Click to expand...

Thank you so much for taking time to help. Unfortunately, I have already read the article. I got all excited thinking it was just an easy fix, but no joy. I'm guessing in this case, the column the author added wasn't merged into the query, just appended to the output table in Excel. Mine will need to be merged into the query so the prices can be indexed to the materials and then filtered.

Something I noticed: after I create the self referencing query, the column in the output table I want to be able to change values (and formulas) in will not work like a normal excel table. Typically, in the first row of the column in an Excel table, I can add a formula and it will fill the entire column creating a calculated column. This feature is disabled in the output table. I have to drag the formula down to fill the column. Then after a refresh, the values remain (including changes), but the formulas disappear.

I did try to just add a column to output table and use index/match to pull pricing, but I get weird results when output table is filtered as a step in the query.

Thought I figured it out... lol

Was wrong. Nothing I'm trying is working. :tsk:

jfclark27 said:

Thought I figured it out... lol

Was wrong. Nothing I'm trying is working. :tsk:

Click to expand...

Now it is working.

Added a column in excel to output table of QUERY1.
Created new QUERY1(2) loaded as connection only.
Merged QUERY1 and QUERY1(2) as new query.
New query is MERGE1
Loaded MERGE1 to a new worksheet

Added formula to new column on output table of QUERY1 turning it into a calculated column.
Output table of QUERY1 now retains formulas or manually entered data (overwriting formula) on refresh.

What worked was merging queries as new query. Probably didn't need to load to a new worksheet. May have been able to load as a connection only, but getting late. I will try that tomorrow. Hope this helps someone else out...

jfclark27 said:

Now it is working.

Added a column in excel to output table of QUERY1.
Created new QUERY1(2) loaded as connection only.
Merged QUERY1 and QUERY1(2) as new query.
New query is MERGE1
Loaded MERGE1 to a new worksheet

Added formula to new column on output table of QUERY1 turning it into a calculated column.
Output table of QUERY1 now retains formulas or manually entered data (overwriting formula) on refresh.

What worked was merging queries as new query. Probably didn't need to load to a new worksheet. May have been able to load as a connection only, but getting late. I will try that tomorrow. Hope this helps someone else out...

Click to expand...

OK, it was not necessary to load MERGE1 to a new worksheet. Creating a connection only worked fine.

Now for the final solution to my problem... After adding an excel formula to new column to create an excel calculated column, manually entered data will be overwritten by a refresh unless I go to excel table properties and uncheck "Preserve Column Sort/Filter/Layout", hit refresh all, then I can go back to table properties and recheck that option. This makes no sense. I don't want to have to do this every time I start a new bid, and I'll be distributing the new template file to my estimating department. I still need some help if anybody is reading this.

Just to clarify. You want to maintain the manual edits after you've done them, BUT when doing a new bid, allow the manual edits be overwritten? If so, then you need a macro. Not my area of expertise. I would set up a MASTER model that people open and save under a different name...that can also be macro'd by having users fill in the project name and other identifying data on the opening screen and having the MACRO grab that data to name the saved file. You also should assign version numbering to your MASTER that is also printed in the header or footer of the printed bid so that you don't have people grabbing old copies of the MASTER and underbidding jobs.

El Cid said:

Just to clarify. You want to maintain the manual edits after you've done them, BUT when doing a new bid, allow the manual edits be overwritten? If so, then you need a macro. Not my area of expertise. I would set up a MASTER model that people open and save under a different name...that can also be macro'd by having users fill in the project name and other identifying data on the opening screen and having the MACRO grab that data to name the saved file. You also should assign version numbering to your MASTER that is also printed in the header or footer of the printed bid so that you don't have people grabbing old copies of the MASTER and underbidding jobs.

Click to expand...

Thank you for the reply.

I have a solution, but it is buggy.

All I wanted to do was to add a column in Excel to an output table from Power Query that I can transform into a calculated column, and allow for manual cell overwrites without losing the formula in the rest of the calculated column cells on a refresh of the output table. I finally have this working, but there seems to be a bug in Excel, or something else in my design I'm missing.

The final step to get this to work is to open the Table Properties in Excel, uncheck "Preserve Column Sort/Filter/Layout", make a manual edit and refresh output table, then go back to properties and recheck "Preserve Column Sort/Filter/Layout". Then my self-referencing query works as I want. I only have to do this one time, but will have to be done for every new workbook created from the template (I will have to abandon PQ if this is the only option).

For now, the only way around this I can see is to create 5 or so "helper" rows of data in the output table with the calculated column working properly, hide the rows permanently, and save as the template for distribution. I have not tried this yet. May or may not work.