Values hard-coded after power query runs is disloc

Question

I have a table called "COMPANIES" like this:

Company    Type
Lafarge    Cement
Google     IT
Disney     Entertainment

The table is then loaded into Power Query and displayed as table COMPANIES2

In table COMPANIES2 in Excel view, I have added a new column with hard-coded text like this:

Company    Type           Country
Lafarge    Cement         France
Google     IT             US
Disney     Entertainment  US

Now back at table COMPANIES, a new row is added:

Company    Type
Lafarge    Cement
Toyota     Cars
Google     IT
Disney     Entertainment

When I refresh table COMPANIES2, I get this:

Company    Type          Country
Lafarge    Cement         France
Toyota     Cars           US 
Google     IT             <blank>
Disney     Entertainment  US

As you can see for some reason the value US for Google has shifted up.

I played around with COMPANIES table, for example by switching some rows or renaming them. In the end COMPANIES2 table is always messed up.

Is there a way to tell Excel / Power Query to protect the second table in the same location? Perhaps we can tell Power Query that "Company" is the ID field and is unique on this table? Therefore whatever new value is hard-coded should move around when the Company field moves?

Answer 1

For the record, I am not 100% sure this is actually sane, but it seems like it works.

Load your COMPANIES2 table into Power Query as a Connection Only. Now go to your original COMPANIES2 query (which will not have the "Country" column), merge it against the connection only version, and expand the "Country" column from it.

While this seems like a circular reference at first, I think it works in practice because PQ has to pull the existing data on the table in Excel before refreshing the output, so it will maintain the data you have added in the Country column.

Answer 2

See @MarcelBeug's answer to this earlier posted question. He includes this link to a good video that he made to show how it's done.