Replace Dynamics D365 StateCode by values

[Anonymous]

Hi,

I am working with Opportunity table from Microsoft Dynamics D365.

In the application, the field Status can have any of those values:

• Open
• Won
• Lost

When I am using D365 as a data source in Power BI, I am accessing a different layer of the database, where the field Status is named "StateCode", and the values are:

• 0
• 1
• 2

My goal is to have in Power BI the values that are visible in the application.

Questions:

• Is replacing the values the right thing to do or is there any other solution ?
• If it is actually the best thing to do, how would you recommend to do it ?

Thank you!

Thomas.

[Anonymous]

HI @Anonymous,

I'd like to suggest you create a status table with state code and description, build relationships from this table to all of statuscode columns.

After this step, you can use 'description' column to replace status code column on visualization to display more clearly status description.

Regards,

XIaoxin Sheng

[Anonymous]

Hi @Anonymous

Thanks a lot for your reply.

That is a good idea, but I'm worried about performance, and I'm wondering if replacing the values directly in the table would make calculations faster ?

What do you think ?

Thank you.

Thomas.

[Anonymous · Accepted solution]

Hi @Anonymous,

Create one dimension table based on unique records from all related table columns and build relationships obviously will affect performance.

If you can direct modify source data, it should more quickly than my suggestion. 

If you mean do replace operation on power query side, it should complex and low performance.(you need to create a dictionary table to compare and replace state on all of tables who contains state column)

Regards,

Xiaoxin Sheng