Forum Discussion
'Replace Values' is Not Generating Expected Results
Ok, this took some deciphering.
If I understand you correctly, you've replaced the values in the 'State' column and you're happy with that. However, you seem to be expecting some automatic process to recognise the 'State' values and populate the 'State Category' in the same way they are populated further up the dataset.
Unfortunately, Power Query doesn't work like that. You're going to have to explicitly instruct Power Query (either by using the interface or writing M code) to do that.
It should be straightforward.
You can write a big 'if' statement to add a new column - this would contain all the cases you want.
OR
You can use a lookup table (either by duplicating the table, removing all columns except state, stae category then remove duplicates OR creating your own using 'Enter Data'). You would Merge the original table and the lookup table using inner join on 'State' to return the state category column.
Does that make sense?
Community ChampionOk, this took some deciphering.
If I understand you correctly, you've replaced the values in the 'State' column and you're happy with that. However, you seem to be expecting some automatic process to recognise the 'State' values and populate the 'State Category' in the same way they are populated further up the dataset.
Unfortunately, Power Query doesn't work like that. You're going to have to explicitly instruct Power Query (either by using the interface or writing M code) to do that.
It should be straightforward.
You can write a big 'if' statement to add a new column - this would contain all the cases you want.
OR
You can use a lookup table (either by duplicating the table, removing all columns except state, stae category then remove duplicates OR creating your own using 'Enter Data'). You would Merge the original table and the lookup table using inner join on 'State' to return the state category column.
Does that make sense?