Connect Power BI Template to Dynamics365

Enhancing the data from Anywhere365 with information from Dynamics365.

Prerequisites

  • Power BI Desktop
  • Power BI report with Anywhere365 data

 

Quick Setup

Combining the dialogue information with caller information can be a strong advantage. Make sure you have installed Power BI Desktop and have a report with Anywhere365 data setup. If you need help creating setting this up, Learn More

 

This guide is focused on connecting the Contacts table from Dynamics to the Power BI Dialogue View based on caller data. For this guide, two additional parameters are created in power BI. This is not mandatory, but can be useful if you are planning to build multiple connections. These parameters are:

  • Dynamics365API : the api that is used to retrieve data
    • For example: https://Domain.api.crm4.dynamics.com/api/data/v8.2/
  • Dynamics365URL: the url to create a clickable link to the contact page
    • For example: https://Domain.crm4.dynamics.com
  •   DynamicsContacts_AccountID: the unique identifier field of the contacts table

You can also replace the parameters with the string values of your API, URL and ID field.

 

Quick setup is useful if you just want caller information such as name or company. If you want to choose specific field or filter you can change the applied steps.

  1. Select Get Data
  2. Select Blank Query

  1. Select Advanced Editor

  1. Copy the following code

let

Source = OData.Feed(Dynamics365API),

Dynamics_Contact_Table = Source{[Name="contacts",Signature="table"]}[Data],

#"Removed Other Columns" = Table.SelectColumns(Dynamics_Contact_Table,{"telephone1", "emailaddress1", "lastname", "firstname", DynamicsContacts_AccountID_Field , "jobtitle", "fullname", "contactid"}),

#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{DynamicsContacts_AccountID_Field, "AccountID"}}),

#"Trimmed Text" = Table.TransformColumns(#"Renamed Columns",{{"telephone1", Text.Trim, type text}, {"emailaddress1", Text.Trim, type text}}),

#"Lowercased Text" = Table.TransformColumns(#"Trimmed Text",{{"telephone1", Text.Lower, type text},{"emailaddress1", Text.Lower, type text}}),

#"Added Custom" = Table.AddColumn(#"Lowercased Text", "MergedContactInfo", each if [telephone1] <> null and [emailaddress1] <> null then [telephone1] & ";" & [emailaddress1] else if [telephone1] = null and [emailaddress1] <> null then [emailaddress1] else if [telephone1] <> null and [emailaddress1] = null then [telephone1] else null),

#"Added Custom1" = Table.AddColumn(#"Added Custom", "NewContact", each if [MergedContactInfo] is null then null else Text.Split([MergedContactInfo],";")),

#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"telephone1", "emailaddress1", "MergedContactInfo"}),

#"Expanded NewContact" = Table.ExpandListColumn(#"Removed Columns", "NewContact"),

#"Added Custom2" = Table.AddColumn(#"Expanded NewContact", "Anywhere365Link", each if [NewContact] is null then null else if Text.Contains([NewContact], "@") then "sipThe Session Initiation Protocol (SIP) is a protocol to make multimedia communication (audio, video and other data communication) possible and it is also used for Voice over IP (VoIP). SIP has similarities with other Internet protocols such as HTTP and SMTP.:" & [NewContact] else "tel:" & [NewContact]),

#"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"NewContact"}),

#"Filtered Rows" = Table.SelectRows(#"Removed Columns1", each [Anywhere365Link] <> null and [Anywhere365Link] <> ""),

#"Trimmed Text1" = Table.TransformColumns(#"Filtered Rows",{{"Anywhere365Link", Text.Trim, type text}}),

#"Grouped Rows" = Table.Group(#"Trimmed Text1", {"Anywhere365Link"}, {{"RowCount", each Table.RowCount(_), type number}, {"FullName", each List.Median([fullname]), type text}, {"AccountID", each List.Median([AccountID]), type text}, {"ContactID", each List.Median([contactid]), type text}}),

#"Removed Duplicates" = Table.Distinct(#"Grouped Rows", {"Anywhere365Link"}),

#"Added Custom3" = Table.AddColumn(#"Removed Duplicates", "Contact URL", each if [RowCount] > 1 then null else Dynamics365URL & "/main.aspx?etc=2&id=%7b"&[ContactID]&"%7d&pagetype=entityrecord")

in

#"Added Custom3"

Note Please note that your Dynamic365 environment might contain test cases or irrelevant phone numbers, they can be filtered out in the Query editor.

 

When you implemented the query and the preview is loaded, meaning there are no issues, you can close and apply the query.

The final step is to create a relationship between the Contact table and the Dialogue table. Navigate to the Model tab; simply click the Anywhere365Link field and drag it to the Caller field in the other table. This will create a many to one relationship and makes the caller information available for you to use.