Friendly names - How to sub out data value with a more readable value?

[krypto6969]

Hi 

I have a SQL query based power bi dash where one of the values is a SQL job. Unfortunatley the SQL job is described like this:

I would like to swap out the text values in the grid that starts with 'SQL Agent - TSQL Job Step 546B1544-9BC0-48B8-956E-6B797633E5B2' with the text value - 'TSQL JOB 'FREIENDLY JOB NAME' .

Do I need to create a lookup table to do this? Or is there a way to do a translate for the 20 or so values - iif text = 'TSQL JOB 545B..' set value to 'TSQL FRIENDLY JOB NAME....' 

[Anonymous · Accepted solution]

Hi @krypto6969 ,

You can use SUBSTITUTE function with similar DAX formula as below.

= SUBSTITUTE([program_name], "SQL Agent - TSQL Job Step 546B1544-9BC0-48B8-956E-6B797633E5B2", "TSQL JOB 'FREIENDLY JOB NAME")  

You can also replace values in Power Query Editor.

Replace values (Power Query) - Microsoft Support

If you want to bulk replace in Power Query, please refer to

Bulk Find And Replace In Power Query | How To Excel

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Nathaniel_C]

Hi @krypto6969 ,
If I understand what you are asking, you can go to Power Query - Transform tab - Find and Replace as in the picture below.

or you can go to Add a Column tab and select conditional column as below.

Or you can add a table with the friendly names and use the LOOKUPVALUE() function.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel