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]

maybe "link" (merge) these columns to the source data by Index eg. Table.AddIndexColumn(previous_step, "Index", 1, 1)
hope all your source data are tables

 

[seti69]

maybe "link" (merge) these columns to the source data by Index
hope all your source data are tables

thanks for your reply. Yes my source data are tables. I use the contract number as index to merge tables so probably don't need to add another index column (except if I did not understand exaclty what you are proposing)
Maybe you are referring to a solution similar to this one, "Self referencing", which basically is merging the columns with the source data in a new data set ? Self Referencing Tables in Power Query - Excelerator BI

This kind of works, but it is unflexible - the user can't add new columns directly in the spreadsheet, and can't add comments to the cells (because they are not linked to the data). Also my file shot up from 1 MB to 13 MB when I added these new queries, although I did not add any data.
So I thought there should be a better way to do this...

 

[sandy666]

I said Index because I didn't know you've key column
the best way to explain will be create representative example of source data and expected result and post a link to the shared excel file via onedrive, googledrive, dropbox or any similar service
anyway you'll need to merge your data with comments column but maybe I am wrong and didn't understood your description

 

[seti69]

Thanks for the advice, I put an example file here : Power Query example.xlsx (let me know if it doesn't work)
The three sheets London, Paris, Berlin represent the steps I have tried. I added comments in the sheets to explain the issue in each case.
Many thanks for your help!

 

[sandy666]

maybe like this for eg. Paris (just with data types))
- add comment column
- select whole table
- from table
- do what you want, filter etc.
- load to the sheet
but don't touch first Paris table except adding comments but refresh second table only

s666-PQ-Power Query example.xlsx

Microsoft Excel Workbook

1drv.ms

 

[seti69]

thanks for the file and your time. However, this way I still need to change the values in the first Paris table - the second one is only for reading (any changes to the data in this table being erased upon refresh)
Also, if I change a value in the main database, it is not passed on to the second Paris table unless the first Paris table is refreshed.
I guess it is not possible to have in one table both reading with Power tools (data from another table), and writing (user-entered data)... or another idea maybe?

 

[sandy666]

first Paris you can update/refresh in Power Query Editor (not from the sheet or queries becuase it will change the order of comments)
then refresh (normal way) second Paris
I'll look at this a bit later for more tests

 

[sandy666]

re-download file from post#6
there is an Index solution but (!) to add comment you'll need use standard table action: TAB or drag down
you can't eat cake and have cake ?

 

[seti69]

(replying to #8)
Indeed, but as this file will be used by people not familiar with Power Query I want all the edits and manipulation to be done in Excel...
But I think I have found a workaround. In one of my added columns in my table I copy-paste my index column (with the contract numbers). So in case the user changes the order of the rows in the Query part, then refreshes, we will still have a reference across the user-entered columns. And in this case we can sort by this new column, then Refresh and it comes back to normal.

Power Query example 2.xlsx

drive.google.com