0

Hi I'm still new to power query and I'm still trying to understand how it fully works.

The goal that I'm trying to achieve is to create a table that list my employee data (ID, name and location) from a database and adding several columns to the side for another department to fill in the details. Currently I have made this using a self referencing table.

However some of those added columns are using formulas to calculate certain values. I added these formulas in excel, not in power query. The reasoning for this is that the user can see the values update immediately after inputting in the columns. In doing so whenever my query is refreshed, these formulas will get overwritten with values, and when new values are added or changed the totals aren't updated.

I know I can add these formulas in power query, but this will lead to the values not updating automatically, unless it is refreshed after each input. I also tried adding the formulas as string in power query, but it does not change into a formula automatically, it needs to be entered in each cell to calculate.

Current excel setup, in here the first 3 columns are from original database, and the rest are added columns that users are manually inputting through here. And the final column contains the formula to total the values of each row automatically.

Any ideas or tips on how I should change this?

Edit1: By self referencing table I meant something similar to this. Where a column is added to the side of the table and is able to keep track to the corresponding ID.

Ario Nugroho Suprapto
  • 11
  • 3
  • Not sure I understand. Refreshing a query will only refresh the table columns which form part of the query. Additional table columns, including those containing formulas, will not be affected. So only the first 3 columns should be loaded as part of your query. And not sure at all what you mean by "self referencing table". – Jos Woolley Nov 26 '21 at 05:30
  • By self referencing I meant [something like this](https://www.youtube.com/watch?v=duNYHfvP_8U&ab_channel=MarcelBeug) where my first 3 columns are updated with employee data that can shift around or add new ones. The next few columns are inputted manually by a user – Ario Nugroho Suprapto Nov 26 '21 at 08:07
  • Please provide enough code so others can better understand or reproduce the problem. – Community Nov 29 '21 at 15:49

0 Answers0