Forum Discussion

sv11's avatar
sv11
Helper I
7 years ago
Solved

Tables Join

Hi,   I have 2 different tables pulled from different system:         1. CRM - Contact         2. Phone Call data   There is no direct link between these 2 systems except "Phone Number".   Is...
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi sv11 ,

     

    Based on your data sample, you could do some change for your CRM data model in Query Editor.

     

    You could unpivot the columns for Phone1,Phone2,Phone3 and then rename the value to PhoneNo.

     

    Then you could create the relationship between the tables with PhoneNo.

     

     

    More details, you could refer to my attachment.

     

    Best Regards,

    Cherry

v-piga-msft's avatar
v-piga-msft
Resident Rockstar
7 years ago

Hi sv11 ,

 

Based on your description, it seems that you want to join the two table from different data source in Power BI.

 

If it is convenient, could you share the sample data which could reproduce your scenario so that I could have a test based on it.

 

Best  Regards,

Cherry

  • sv11's avatar
    sv11
    Helper I
    7 years ago

    Hi v-piga-msft ,

     

    Please see the 2 pics below:

     

    1. Call Data - is the data from our phone systrem which tracks all calls made by sales team

     

    2. CRM Data - is the contact information of our clients, source for sales team

     

    Thanks

    • Aron_Moore's avatar
      Aron_Moore
      Solution Specialist
      7 years ago

      If the number of phone # columns is fixed you could try a calculated or custom column.


      In Query editor it would something like PhoneKey = If Phone1 <> "" then Phone1 else if Phone2 <> ""....etc

      In DAX you could use SWITCH()

       

      Then join or lookup using the PhoneKey column.

    • v-piga-msft's avatar
      v-piga-msft
      Resident Rockstar
      7 years ago

      Hi sv11 ,

       

      Based on your data sample, you could do some change for your CRM data model in Query Editor.

       

      You could unpivot the columns for Phone1,Phone2,Phone3 and then rename the value to PhoneNo.

       

      Then you could create the relationship between the tables with PhoneNo.

       

       

      More details, you could refer to my attachment.

       

      Best Regards,

      Cherry