How to extract the correct code

[PBI_newuser]

Hi,

I have a list of problem code and I wish to extract the information C162, C200 and C198 from the list.

How can I do that?

Example:

1. DS (Dispenser) (C162) --ComA
2. C200 Mechanical
3. Hardware (C198) --Default

[PhilipTreacy]

Hi @PBI_newuser 

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

[PhilipTreacy]

Hi @PBI_newuser 

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.

[PBI_newuser]

Hi @PhilipTreacy, I can't create the lookup table as I have more than 1000 cause code and problem code with different format in the table.

[PhilipTreacy]

Hi @PBI_newuser 

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.

[PBI_newuser]

Hi @PhilipTreacy , I don't have 2nd table with the Standardized Code and the Expected Output, but I wish to transform the "Problem Code" to "Expected Output". Is it possible?

For example:

Problem Code

C110 CV's

CV's (C110) --Default

Ouput

C110 - CV's

Thanks.

[PBI_newuser]

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

Problem Code	Standardized Problem Code	Expected Output
Drawer won't open (C747) --BBB	C747	C747 - Drawer won't open
Incorrect / Unexpected Results (C169) --Default	C169	C169 - Incorrect Unexpected esults
Mixtures (e.g. Rack, Tray, Holders) (C163) --Com	C163	C163 - Mixtures (e.g. Rack, Tray, Holders)
Cable / Wiring / Connector (C104) --Default	C104	C104 - Cable Wiring Connector onnector
C110 CV's	C110	C110 - CV's
CV's (C110) --Default	C110	C110 - CV's
Analysis Software (e.g. DiVA) (C150) --Default	C150	C150 - Analysis Software (e.g. DiVA)
Sample (aspiration, dispense) (C186) --A	C186	C186 - Sample (aspiration, dispense)
A&T / Setup (C816) --Default	C816	C816 - A&T / Setup
Others (C200); describe specifics in CAUSE --Default	C200	C200 - Others
C113 Electrical Power	C113	C113 - Electrical Power
Electrical Power (C113) --BBB	C113	C113 - Electrical Power
Electrical Power (C113) --Default	C113	C113 - Electrical Power

[amitchandak]

@PBI_newuser , 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)

[PhilipTreacy]

Hi @PBI_newuser 

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.