Transform Alpha numeric GUID to string values

[tmayank1159]

Hi,

I have imported data from CRM into PowerBI and some values in some fields (columns) are appearing in alphanumeric format which is not what i want.

What could be the problem and how can I resolve. The above picture shows what i mean. Intead of hospital names i am getting these alphanumeric values. 

Help at the earliest will be appreciated a lot as my project is stuck because of this! Thanks a ton in advance !

[RodrigoBrigante]

I believe there is no error in displaying the data in PowerBI.

Instead, the solution might be looking into other tables from this database. Most likely, there is a Dimension table for the Hospitals, so in this table it will only store the Hospital GUID, but the complete information about the Hospital would be stored in a different table.

You would then need to import both tables and create the proper relationship to retreave info about the Hospitals, including its name

Just for example, I will try to depict what those 2 tables would look like:
Table Contacts (I believe this is the name of the table shown in the Print Screen)

CONTACTS
_cn_Hospital_value cn_patienttype cn_patientType_String
1025236d-797c-8113-c43 1 1 - Afiliated
1025236d-797c-0064-c11 1 1 - Afiliated
1025236d-797c-8113-c43 2 2 - Referral
1025236d-797c-0064-c11 2 2 - Referral

The name of the Hospital might be in a different table, lets suppose it is Called HOSPITALS

It might look like this:

HOSPITAL
_cn_Hospital_ID _cn_Hospital_Name _cn_Hospital_State _cn_Hospital_City _cn_Hospital_ZIPCode
1025236d-797c-8113-c43 Hospital Name 01 NY New York 10001
1025236d-797c-0064-c11 Hospital Name 02 CT Bridgeport 06601
1025236d-797c-a4e3-0d3 Hospital Name 03 CT Bristol 06010
1025236d-797c-32ac-f00 Hospital Name 04 RI Providence 02901

Therefore, the Dimension Table HOSPITAL stores all info about the Hospitals, and the fact table Contacts you mentioned just refers to the Hospital name by its ID.

If you import both tables to your PBI model and create a 1 to many relationship between the Hospital table and the Contacts table, you will be able to use the Hospital name propperly.

[v-sihou-msft]

@tmayank1159

What's the data type in your data source? How did you connect the data source? Please try to connect via Odata.

As you said your data is from CRM, your data might be encrypt already so that you can only get the Alpha numeric. In Power BI Desktop, either Power Query or DAX has no function to decrypt this code. 

Regards,

[tmayank1159]

The data type is string only, and it is appearing alright in CRM. We have tried to import data via an api. Many field are in the correct format except these. 

[Anonymous]

Hi there,

Did you find a solution or a workaround to connecting to CRM?

I've ran into the same problem as well.

[RodrigoBrigante]

Just check my answer to the main question.

Most likely this table only returns the GUID, and the real name is actually loacted in a different table of your database. You might have to import both of them into PowerBI and create a relationship to use the name coming from the Dimension Table.