excelpowerqueryexcel-tables

Move entire rows when PowerQuery data change


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.


Solution

  • 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:
    1. Load this merged query back into MembersTable.