How to extract the correct code

Hi Anonymous 

This works with the examples you've supplied.  The query expects all error codes to start with C. 

Copy andpaste the code below into a new blank query or use this  sample PBIX file showing it working

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcglW0HDJLC5IzStOLdJU0HA2NDPSVNDVdc7PdVSK1YlWcjYyMFDwTU3OSMzLTE7MAYt5JBallCcWpYKUW1qAlLukpiWW5pQoxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Problem Code" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Problem Code", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each "C"&Text.Combine(List.RemoveItems(Text.ToList(Text.Lower([Problem Code])),{"a" .. "z", "(",")"," ","-"})))
in
    #"Added Custom"

Phil

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

Anonymous , What output you need is not very clear. Try new column like

if( search("C162",[column],,0) >0 || search("C200",[column],,0) >0 || search("C198",[column],,0) >0,1, 0)

Hi PhilipTreacy , it works!!
Is it possible to standardized the problem code with description (Expected Output) as shown below?

Hi Anonymous 

Yes. If you have a 2nd table with the Standardized Code and the Expected Output, you can join the 2 tables to get the result you want.

Here's a sample PBIX showing this working

If you don't want the middle column in the final table, just delete it.

Regards

Phil

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

Hi Anonymous 

You'll have to create the table, that's how PBI/PQ will know what the output will look like.

See this Excel workbook to illustrate what I mean.  I've created a tale with the Problem codes and another table with the Standardized Problem and expected Output.

I don't know what your data source is but thisshows how to do it in Excel.

You can also load or create a table in Power BI to do the same.

Regards

Phil

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

Hi Anonymous 

Then there is no way to do what you want.  If you can't create a table to tell PBI/PQ what the expected output should be, it won't know what to do.

Regards

Phil