Covert data label codes to easy to understand entries

[ms499]

Hello,

I'm new to the world of Power BI.

So, I have have a database filled with an large array of data.  Much of the cell entries has coded elements to it.  I'm trying to understand how best to convert the code within the cell entries to a entry where codes are transalatable to easy to understand English versions of the original cell entries. 

For instance I have a column that has a range of 168 codes within it.  I want to be able to convert each code to something more understandable / relatable. 

So for instance, I have a code that reads 'H121' in the original data and I want to be able to convert this to it's translatable version which in this case should be 'H121 Fire Safety Engineering'

Is there a way I can achieve this without having to go through each one of the code within the dataset?

With thanks in advance for any help in received.

Thanks and best wishes, Mike.

[PhilipTreacy]

Hi @ms499 

I've created a PBIX file you can download and open to demonstrate the process below

You'll need to set up a table with the codes and the extended code/description, something like this

and if your data looks something like this

then you can merge/join the two together to get a result like this

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.

[ms499]

Hi Phil,

That's brilliant.  Many thanks for this.  Sorry for being padantic but, how do I go about blending / introducing the generated list of codes back into the original dataset and apply the new translatable cell entries into it?

Thanks and best wishes, Mike.

[PhilipTreacy · Accepted solution]

@ms499 

No worries Mike.

The idea with Power Query/Power BI is that the original data remains as it is.  You bring it into PQ/PBI and then transform/merge it with other data, then use the output from those processes to produce reports, visuals etc.

If your data is in a database and you want to add the extended codes/descriptions to that data, you'd need to create a table within the database with the codes and descriptions (my 1st image), and then do the merge/join within the database, basically adding another column to the current data/table.

You'd do this using SQL in the database itself, not through PQ/PBI.

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.