Add a column for user input in power query generated table

Occasional Contributor

I have a table that is filled from a CSV file using a power query. The first column in the CSV is like a "unique identifier" or "key" for a row.

 

In the same table, I have a column named "List 1" and "List 2". These are empty by default and are supposed to be filled by the user. Now the problem is when I refresh the power query, it loads CSV again and in case CSV has a different order of row than earlier, user input of List 1 and List 2 values get assigned to the wrong row.

 

I want it to be smart. Suppose if the user assigns "List 1" = A to a row with the key "AAPL", it should remember that. When I refresh CSV, it should stick with AAPL even though the row number of AAPL has changed within the CSV.

 

 

 

2 Replies

@VarunAgw You need to look into what is known as "self referencing tables". Google gives you plenty of suggestions on how to set these up. This is the first hit I got.

https://exceleratorbi.com.au/self-referencing-tables-power-query/ 

Thanks.
I was able to follow the tutorial and implement it. But it feels very hackish solution.