'Replace Values' is Not Generating Expected Results

[Anonymous]

BACKGROUND: We have a report in Power BI where the data is loaded from Azure DevOps. I am trying to consolidate the values in one of the columns in Power BI (labeled, STATE) so that all the values of that column have a corresponding match in another column (labeled, STATE CATEGORY). Previously, we had used some STATE values that ended up causing some States not map to any State Category. Steps have been taken in Azure DevOps to prevent this from happening in future, but the historic STATE values are there that are in question here.

SAMPLE DATA: Here are the two columns in question:

State	State Category
Versioned	Proposed
Validated	Proposed
Sized	Proposed
Reported	Proposed
QA	InProgress
Proposed	Proposed
New	Proposed
Endorsed	Proposed
Done	Resolved
Development	InProgress
Design	Proposed
Confirmed	Proposed
Closed	Completed
Assigned	Proposed
Active	InProgress
9b. Done
9a. Ready for Deployment
8. Passed QA - Awaiting PM Approval
7a. Code Fixup
7. QA Testing in Progress
6. Ready for QA
5b. Code Review
5. In Development
4b. Committed
3b. Versioned
3. Sized
2. Approved for Development
1. PM/BA Review
1. New

PROBLEM: You can see how the highlighted rows have no corresponding STATE CATEGORY; just blank. Need those State values replaced with the appropriate ones from within the same column, so that all the State values will be mapped to a State Category.

MY APPROACH: In Power BI Desktop > Transform Data, I, simply replaced the highlighted STATE values with standard (pre-existing) ones, like below, by right-clicking the State column, and using "Replace values..." command:

Old Value	New Value
Versioned	Versioned
Validated	Validated
Sized	Sized
Reported	New
QA	QA
Proposed	New
New	New
Endorsed	Validated
Done	Done
Development	Development
Design	Design
Confirmed	Validated
Closed	Closed
Assigned	Assigned
Active	Active
9b. Done	Done
9a. Ready for Deployment	Done
8. Passed QA - Awaiting PM Approval	Done
7a. Code Fixup	Development
7. QA Testing in Progress	QA
6. Ready for QA	QA
5b. Code Review	Development
5. In Development	Development
4b. Committed	Development
3b. Versioned	Versioned
3. Sized	Sized
2. Approved for Development	Validated
1. PM/BA Review	New
1. New	New

EXPECTED RESULTS: My expectation was that by doing so, these unmapped values will be treated as the ‘pre-existing’ State values, and the State column will still have unique values - all of which will map to the corresponding STATE CATEGORY, like below:

State	State Category
Versioned	Proposed
Validated	Proposed
Sized	Proposed
Reported	Proposed
QA	InProgress
Proposed	Proposed
New	Proposed
Endorsed	Proposed
Done	Resolved
Development	InProgress
Design	Proposed
Confirmed	Proposed
Closed	Completed
Assigned	Proposed
Active	InProgress

ACTUAL RESULTS: However, this is the result I am getting: So, Power BI is just creating duplicate State values per instance, but still not mapping the ‘replaced values’ to any State Category. There are more than 500K rows in the dataset, and not having every State mapped to a State Category is causing major issues when running such reports as Total Count of Work Items in “Resolved” or “InProgress” State Category etc.

State	State Category
Versioned	Proposed
Validated	Proposed
Sized	Proposed
New	Proposed
QA	InProgress
New	Proposed
New	Proposed
Validated	Proposed
Done	Resolved
Development	InProgress
Design	Proposed
Validated	Proposed
Closed	Completed
Assigned	Proposed
Active	InProgress
Done
Done
Done
Development
QA
QA
Development
Development
Development
Versioned
Sized
Validated
New
New

REQUEST: Any help or guidance will be greatly appreciated that will help achieve what I have illustrated in the table (under the ‘Expected Results’ section) above. Thank you.

[Anonymous]

Thanks @HotChilli . This really helps. I had the lookup table set up, but inner joins were not set up, correctly. I also appreciate the alternate solution (M code) that I was not aware of. Much appreciated!

Thanks.

Asad.

[HotChilli · Accepted solution]

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?