Tables Join

[sv11]

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

[v-piga-msft]

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]

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

[v-piga-msft · Accepted solution]

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

[Aron_Moore]

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.