Results 1 to 8 of 8

Thread: Self referencing query

  1. #1
    Seeker jfclark27's Avatar
    Join Date
    Aug 2018
    Posts
    6
    Articles
    0
    Excel Version
    excel 365 click to run

    Self referencing query



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,372
    Articles
    0
    Excel Version
    Office 365 Subscription
    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-...p-wyn-hopkins/
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Seeker jfclark27's Avatar
    Join Date
    Aug 2018
    Posts
    6
    Articles
    0
    Excel Version
    excel 365 click to run
    Quote Originally Posted by AliGW View Post
    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-...p-wyn-hopkins/


    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.

  4. #4
    Seeker jfclark27's Avatar
    Join Date
    Aug 2018
    Posts
    6
    Articles
    0
    Excel Version
    excel 365 click to run
    Thought I figured it out... lol

    Was wrong. Nothing I'm trying is working.
    Last edited by jfclark27; 2018-08-15 at 10:59 PM.

  5. #5
    Seeker jfclark27's Avatar
    Join Date
    Aug 2018
    Posts
    6
    Articles
    0
    Excel Version
    excel 365 click to run
    Quote Originally Posted by jfclark27 View Post
    Thought I figured it out... lol

    Was wrong. Nothing I'm trying is working.
    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...

  6. #6
    Seeker jfclark27's Avatar
    Join Date
    Aug 2018
    Posts
    6
    Articles
    0
    Excel Version
    excel 365 click to run
    Quote Originally Posted by jfclark27 View Post
    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...
    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.

  7. #7
    Acolyte El Cid's Avatar
    Join Date
    Aug 2016
    Location
    Greenville, SC
    Posts
    51
    Articles
    0
    Excel Version
    Excel 2016
    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.

  8. #8
    Seeker jfclark27's Avatar
    Join Date
    Aug 2018
    Posts
    6
    Articles
    0
    Excel Version
    excel 365 click to run
    Quote Originally Posted by El Cid View Post
    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.
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •