Power Query - Add Data to Loaded Table.

Copper Contributor

Hello.

 

This might be a bit of a newbie question. Hopefully there's an easy answer:

 

I'm loading data into Excel from an external data source using Power Query. In the resultant table in Excel, I want to add a column where I can enter my own free-hand data (text), which does not exist in the source.

 

If I add a column to the table in Excel, populate it with text, then the source changes in a way that inserts a row higher up in the table on refresh, it doesn't push the values of my manually added columns down the corresponding number of rows and they no longer line up with their original rows.

 

I tried adding an extra column to the query with the value null but on refresh it just wipes the data I entered.

 

What's the solution for this problem?

1 Reply

@wilheim 

That's not obvious task, column added to the table returned by Power Query is not synced with it on refresh. The workaround is

- you need to have some unique ID in the table returned by Power Query.

- add manually column with comments

- without refresh query result (actually you query result of query), but that shall be done as separate query, as another step in initial one

- merge first table with this one on ID, do cosmetic and return result to Excel sheet on the same place

 

I lost the link on post(s) with step by step instruction on how to do that, try to google for it.