2

I'm working on a file with two different tables on two different Queries, with this structure:

Table 1

GROUP MEMBER ID GROUP SCORE 1 SCORE 2 SCORE 3
Member 1 01 A1 1 1 4
Member 2 02 A1 3 3 5
Member 3 03 A2 4 1 2
Member 4 04 A2 5 5 5

Table 2

GROUP MEMBER ID GROUP SCORE 1 SCORE 2 SCORE 3
Member 5 05 B1 1 1 4
Member 6 06 B1 3 3 5
Member 7 07 B2 4 1 2
Member 8 08 B2 5 5 5

Now, the first 3 columns from the left (Group Member, ID and Group) are created with the Queries, but all the other columns are just simple columns added by me in the excel sheet, so they're not connected to any Query.

If I change in the root file for the Query (is the same for both) for example the group of a member, all the tables correctly update, but only for the first 3 column.

For example, if I change Member 2 group from A1 to B2, the result:

Table 1

GROUP MEMBER ID GROUP SCORE 1 SCORE 2 SCORE 3
Member 1 01 A1 1 1 4
Member 3 03 A2 3 3 5
Member 4 04 A2 4 1 2

Table 2

GROUP MEMBER ID GROUP SCORE 1 SCORE 2 SCORE 3
Member 5 05 B1 1 1 4
Member 6 06 B1 3 3 5
Member 2 02 B2 4 1 2
Member 7 07 B2 5 5 5
Member 8 08 B2

So the Member is correctly moved to the GROUP B table, but all the scores are now wrong, because they were not shifted with the correct member.

How can I fix that? Is basically the most important part of this file since I should manage something around 200 members divided in 4/5 groups and would be the best to have those sorted automatically with just a single change in the main file.

Improve this question
1
  • 2
    Whatever you do, don’t manually add columns next to a Power Query table and expect them to stay in sync. They won’t. Every time the query refreshes, the table might get rebuilt or reordered, and your manual entries will end up attached to the wrong rows. A better way is to store the scores in a separate table. Just a simple one with columns like ID, Score 1, Score 2, and Score 3. Load that table into Power Query and merge it with your main member table using the ID as the key. That way, the scores always stay linked to the right person, even if the group or order changes later. Commented Aug 5 at 6:44

1 Answer 1

Reset to default
2

When you load a Power Query table to Excel, the refresh process completely replaces the output range with fresh data from the query. Any columns you add in Excel that aren’t part of the query have no relationship to the rows coming from Power Query. They’re just cells sitting next to the query output — so when rows shift, your manual data doesn’t “follow” the matching member.

What you can do is to use a self-referencing table (this method required each record to have a unique ID which exists in your data):

  1. Load your source data into Power Query (SourceQuery).
  2. Load it into an Excel Table (MembersTable).
  3. Type your scores into MembersTable.
  4. Create a new query:
  • Reference SourceQuery (latest member/group data).
  • Reference MembersTable (existing data + scores).
  • Merge them on the ID column.
  • Keep the scores from the Excel table, the member/group data from the source.
  1. Load this merged query back into MembersTable.
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.