1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Maintain data table that receives data from query and user input

Discussion in 'Power Pivot, Power Map etc' started by Shawn Miller, Dec 20, 2018.

  1. Shawn Miller

    Shawn Miller New Member

    Messages:
    22
    Hello,

    I am trying to figure out a way to have a data table that both refreshes based on a Get/Transform query (power query), and also maintains user input data in fields/columns that I have added to the far right of the results query table. Is there a good way to maintain a data table with both of these inputs and prevent data from shifting or getting deleted?

    Attached is an example file with a financials table and also a table that is a query off of that financials table. If you follow my testing instructions, what happens is the manual data (user inputted data) will "shift" and now be wrong, the amounts in red to the right wont line up with the correct row after refreshing.

    Ultimately my company has a cumbersome quarterly forecasting process that involves ~30 different files that finance refreshes with updates (actuals) and then manually consolidates when ready to roll up for summaries. I am trying to use Data Tables and Get/Transform to push down updates as well as using a combine query to pull all these together and this will help. If I can somehow figure out this last piece I should have a much improved process we can use.

    Any ideas or suggestions would be greatly appreciated!

    Thanks,
    Shawn

    Attached Files:

  2. Nebu

    Nebu Excel Ninja

    Messages:
    2,135
    Hi:

    I do not think what you are trying to do is logical because there is no link established between the table in Financial tab and ABC Cost Centre tab. Even though both the tables are same, Excel fails to identify the rows and establish a relationship when you enter forecasting values manually. Here is what I have done with your file, find the attached, yo may want to look at Sheet 3.

    Thanks

    Attached Files:

  3. Shawn Miller

    Shawn Miller New Member

    Messages:
    22
    Thank you Nebu. I am not strong with Get/Transform...how and what did you do? I cant replicate it because it gives an error trying to find the file on your machine. It looks like you did a function to expand columns (add my 3 columns for user input) on the tbl Financials query. I wasnt sure how to apply a function to a query.
  4. Nebu

    Nebu Excel Ninja

    Messages:
    2,135
    Hi:

    I have made the file much simpler.

    Thanks

    Attached Files:

    Thomas Kuriakose likes this.
  5. Shawn Miller

    Shawn Miller New Member

    Messages:
    22
    Nebu,

    Thank you for the update, this test file appears to do as desired (yay!) and I am trying to re-create it. I think your update works because in the query under "changed type" you have the manually created fields included (the columns listed as forecast). How did you modify or create the query so that it includes these fields? I tried modifying the code, adding the fields separately, etc.

    When I edit your query I get the error "The queries in this workbook might be incompatible with your current version..." I can see see the applied steps but get this error. We have Office 365 so I have Microsoft Office Professional Plus 2016 and I am using the Get and Transform part of Excel. Am I missing anything that I would need like a plug in?

    I really appreciate your help!

    Thanks,
    Shawn
  6. Nebu

    Nebu Excel Ninja

    Messages:
    2,135
    Hi:

    Don't worry about change type that is a default step that power query creates I have not done that step manually. I have not done anything fancy, I just added an additional step to filter for ABC from your raw table, the coloured portion in your raw table is where you will manually enter your forecast values. The output table is just the table filtered for ABC.

    Thanks
  7. Shawn Miller

    Shawn Miller New Member

    Messages:
    22
    Thank you Nebu, I have been experimenting with Get and Transform and getting more familiar. I set this up incorrectly. I want to be able to have an Excel table that receives updates from Get/Transform query (my 2nd tab in my example file) while also have fields to the right that can be user input fields. The goal would be to then be able to refresh the query and that table "resize" but the user input fields would stick with the rows/columns they were entered on. It doesnt appear there is a way to do this.

Share This Page