Replace entire cell if it contains a certain value

[ABR002]

So I'm dealing with a document with several thousand codes. I need to classify them in multiple ways. As an example:

AAAD2900234

AAAD2800244

AAAD3800212

Basically, the 3rd and 4th digit represent a district, for example D2 might be Southwest and D3 might be Northeast, and then digits 5-7 represent the manager in that area, so 900 might be Smith, and 800 might be Jones. The last 3 digits are just a project identifier, nothing gets done with them, they can go away.

What I'm trying to do is basically duplicate the column with the codes, and I want to replace any instance of D2 with Southwest, so now the cells within the new column like AAAD2900234 would now just say Southwest, and all instances of 800 change the entire code to Jones.

I actually know how to do what I described above, but the way I know how to do it would complicate adding new codes which happens every month. I found some code to try but keep getting errors like expected token or invalid something. I'm not sure if there's another way. Basically in replace values under advanced there's Match entire Cell Contents, I'd like a Replace entire cell contents.

Thanks

[v-juanli-msft]

Hi @ABR002 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?

If not, please feel free to let me know.

Best Regards

Maggie

[mahoneypat · Accepted solution]

Assuming you have lots of these to replace, I would do this in the following way:

1. Add two custom columns with Columns By Example (or a custom column with Text.Start() and Text.End()) to get the characters for your districts and managers.

2. Create two queries/tables with your substitution values for each

3. Merge the two tables into your original table on the respective columns

4. Expand the needed fields and remove the columns you no longer need

5. Disable load on the two queries so they are not part of your data model

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[lbendlin]

You will want to have external reference tables for districts and managers.  Your Power Query code can then use these for the substitutions/value of the additional columns, and you can maintain the reference tables as needed, independent of your .pbix file.