Power Query from sharepoint list row shifting error

[acmwib]

I run a query in Excel to get data from a Sharepoint list. In the columns to the right of the sharepoint data in excel, I have manual entries that I need to remain linked with the same row through data refreshes. Right now, the manual entry on the last row just keeps being moved to the new last row when new items are added. For example, I can start with something that looks like this in Excel:

Sharepoint Column     Manual Entry Column

Item 1                           A

Item 2                           B

If I add an Item 3 to the Sharepoint list and then refresh the data in excel to import it, I end up with 

Sharepoint Column     Manual Entry Column

Item 1                           A

Item 2                           

Item 3                           B

What I'm going for is

Sharepoint Column     Manual Entry Column

Item 1                           A

Item 2                           B

Item 3                           

Hopefully that makes sense. I'd really appreciate any insight on this problem

[edhans · Accepted solution]

If I understand the issue, you cannot do this. The data coming from Power Query will not retain the same row number as manually entered data.

You will need to either put your manual data in another table with some sort of index that you can merge on - so "Item 1" for example. 

Then in PQ, merge the SharePoint table with the Excel manual table and use your manually added column.

You can see more about merging here. Merge queries (Power Query) (microsoft.com)

[Greg_Deckler]

Ah, good catch @edhans. After reading the post through a couple more times, I think you are dead on.  

[Greg_Deckler]

@acmwib What does your Excel query look like?