Excel column values assigned manually changes upon refresh

[nikhil0511]

My excel sheet contains two columns, first column loads via power query and second coulmn I will be assigning the values. But, the values I assign keeps changing upon refresh as new values are added to query loaded column. 

I need a solution for this as I'm having two excel sheets like this and they are keep messing around my results.

Category values are loaded via power query and i will assign the factor values to them. 

Please note: this table values are again loaded to my data model.

[nikhil0511]

Hi @MFelix 

Thank you I have resolved this issue by the concept self-referencing query or table. 

but now I want to set the blank values to "1" when they refresh not messing around the other values.

for example say a new row of category as 'books" added after refresh, then the factor value is blank, instead it should have a default value of 1

can this be possible or do i need to manually assign the values to the new;y added rows?

[MFelix · Accepted solution]

Hi @nikhil0511 ,

On the query editor select the column of the factor and then make use of the replace values on the value to find write null and 1 on the Replace with:

If you don't have null but have blank then don't write anything on the value to find

[MFelix]

Hi @nikhil0511 ,

I assume you are placing the values in your table and they are getting in sorting A to Z in this case everytime you add new values the columns you add in excel are not bound to the query so it get's messed around.

This is because altough you have the value in the same table in excel when you do the refresh and add the new data the order of the columns are only for the values of the query there is no relation between the query data and the "new column" this new factor should be added in the query editor to avoid this missmatch.

One option is to after you have made the changes create a copy of the file but wiht values only and then make the merge of this file with the previous one in order to always pick up the factor that you added then the cells that are empty are the ones you need to fill, then saved the text only file with the new values.