Power Query and additional manual columns

seti69

New Member
Joined
Aug 27, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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 !
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,126
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
 
Last edited:

seti69

New Member
Joined
Aug 27, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 24, 2015
Messages
7,126
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

New Member
Joined
Aug 27, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Oct 24, 2015
Messages
7,126
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
 

seti69

New Member
Joined
Aug 27, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Oct 24, 2015
Messages
7,126
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

Well-known Member
Joined
Oct 24, 2015
Messages
7,126
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 :cool: 😇
 

seti69

New Member
Joined
Aug 27, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
(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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,486
Messages
5,548,337
Members
410,828
Latest member
A9Bosv3
Top