Maintain data table that receives data from query and user input

[Shawn Miller]

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

 

[Nebu]

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

 

[Shawn Miller]

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.

 

[Nebu]

Hi:

I have made the file much simpler.

Thanks

 

[Shawn Miller]

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

 

[Nebu]

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

 

[Shawn Miller]

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.