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".

 

Issue:

"CRM table" has over 11 columns with Phone number infirmation (Eg: Mobile 1, Mobile 2, Work Phone, Home Phone...)

And "Phone call" data is the actual number used to call the customer (Pulled from phone database). This number can exists in any of the column in CRM table and is quite random for different clients.

 

 

Is there any way (Through Power Query) to transform CRM data and enable the join between these 2 table?

 

I appreciate your help.

Thanks

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

4 Replies

  • 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.