Dialogue Cloud

Connect Power BI Template to Dynamics 365


Enhancing the data from Anywhere365 with information from Dynamics 365.



  • 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

  3. Select Advanced Editor

  4. Copy the following code


    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 "sip:" & [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")


    #"Added Custom3"

Note Please note that your Dynamic 365 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.