Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Replace Dynamics D365 StateCode by values

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.

 

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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