Power Query and additional manual columns

[seti69]

Hi Excel experts,

I have an issue which I thought was simple, but after hours of searching I can't find a good solution.

I have a database sheet with data for a lot of contracts for about ten sales offices.

I would like to create one sheet per sales office that get some of the data from this database in a dynamic way, and with additional custom columns (not linked to the database) filled manually by each sales office.

At first I used INDEX-MATCH functions to get the data from the database sheets, it works but is inefficient for a large amount of data. So I tried to use Power Query (first time) and it works great and way more efficient to pull the data.

However the problem is with the additional manual columns. They seem not to be linked with the rest of the table, so when I changed the order of the rows and do a refresh of the query, they do not match anymore with the rest. Am I missing something?

I have experimented also with Power Pivot, but I am not sure whether this is the right path, since I don’t want a pivot table but a regular one…

Many thanks for your solutions or hints on what would be the right tool to do this !

 

[sandy666]

people not familiar with Power Query

there is nothing with PQ for them, just add comment with standard Table behaviour, that's all
but if your solution works for you that's good

have a nice day

 

[seti69]

Yes, so far it seems to be working as I want.
Many thanks for your help!!
Have a nice day