Forum Discussion
Excel Power Query
Two things...
1. I have a main sheet tab that takes information from 3 other sheet tabs in the same workbook and displays the data using power query. However, I also want to add additional free-text columns to the main sheet tab but whenever I refresh the query the free-text columns reshuffle. Is there a way to add additional columns to this tab without the information reshuffling every time I refresh? The information being pulled via the query is fine it just seems to be the extra columns that move.
2. I also want to pull data from the main sheet tab to the other 3 sheet tabs. I have tried using the VLOOKUP formula on the other 3 tabs but I get a spill error (presumably this has something to do with the main sheet being a query tab?). Is there a way to fix this?
4 Replies
In my testing sample, I try to add 1-2 supporting column in 3 subsheets. Such as a input date.
When I run the Append as New I will sort the date as 1st sort and sort ColumnA as 2nd sort.
The the manually input will not shuffle. And you could easily vlookup back to subsheets.
If your subsheet is very complicated you could add 3 support columns date, sheet A/B/C and row number.
Farrah_Twigg On 1, there are several techniques to achieve this. Google for "manual column power query" and you'll find them. E.g. this one:
On 2, a SPILL error suggests that the formula tries to output to more than one cell at once and that the output range is occupied by non blank cells. Alternatively, you have entered a dynamic array formula inside a structured table.
Riny_van_Eekelen this, unfortunately, doesn't solve the problem for what I'm trying to achieve.
Effectively, what I am trying to create on Sheet A. is a bingo matrix for a sales team to see all of their accounts and what products/services they have. Sheets B, C & D have the technical information for particular products. My Power Query tab (Sheet A) is pulling through the customer name and a total column from sheets B, C & D. What I then want to do on Sheet A is add extra columns for information that the sales team can populate.
Farrah_Twigg Difficult to help without seeing the file. Hard to imagine what a "bingo matrix for a sales team" is. And as said, there are other methods to do such things in PQ.
