Dialogue Cloud

Connect Power BI Template to Dynamics 365

Introduction

This guide is written to connect the Anywhere365 Power BI Template with the Dynamics365 CRM information. Although this guide connects the data with Dynamics365, it can be used to connect to any CRM system that Microsoft Power BI is able to connect to. Keep in mind that the Power BI connector and/or data tables might differ from this guide.

 

Prerequisites

  • Power BI Desktop application, updated with the September 2021 package

  • Anywhere365 Template v 8.2.21382.01 (or later) loaded with data

  • Connection string to Dynamics365 CRM data source

  • Account with read access on the CRM data source

 

Notes

  • Depending on the CRM system/version that is used, fieldnames might differ. Please advise with the documentation or administrator of the CRM system which field should be used.

  • Combining the data from a CRM system requires the data in that system to be maintained properly. If any data is formatted wrongfully or spelled incorrectly, the relation between the two sources might not work properly and phone numbers/sip addresses might nog be recognized.

 

Guide

  1. Open the Anywhere365 Power BI template in Power BI Desktop

  2. Under the ‘Home’ tab, select ‘Get Data’. A pop-up screen will appear.

    Either search for Dynamics 365 or OData Feed. Both options work, as the Dynamics 365 uses that OData connection in the Power Query.

  3. Fill in the connection API URL, leave the selection set to ‘Basic’ and press ‘OK’

  4. Because Dynamics 365 is home to many tables, use the search bar to locate the table ‘Contacts’. Select the checkbox left of the table name and press ‘Transform Data’.

    If you have selected ‘Load’ instead of transform data, navigate to the Power Query editor by selecting ‘Transform Data’ in the home screen.

  5. The Power Query Editor will open. Under the ‘Home’ tab, select ‘Choose Columns’

  6. Rename the query on the left side of the screen, in the Queries panel. For this guide the table will be called Dynamics365 Contacts. This name will be used to locate the table in the data model later in this guide.

  7. Any field can be chosen to combined with the Anywhere365 CDR data, but these fields are mandatory:

    1. ContactID

    2. Telephone number (in this guide called ‘telephone’)

    3. Email address (in this guide called ‘emailaddress’)

    4. Full name (in this guide called ‘fullname’)

    Recommended (optional):

    1. AccountID (the field which connects the contact table to the account table in your CRM system. This will allow for further combining data through this field)

  8. Select the Telephone column. Under the ‘Transform’ tab, select ‘Format’ and ‘Add Prefix’

    Because Anywhere365 adds ‘tel:’ in front of all telephonenumbers, this has to be done in Power BI as well. Fill ‘tel:’ without the quote marks into the field and press OK.

  9. Select the Emailaddress column. Under the ‘Transform’ tab, select ‘Format’ and ‘Add Prefix’

    Because Anywhere365 adds ‘sip:’ in front of all sip uris, this has to be done in Power BI as well. Fill ‘sip:’ without the quote marks into the field and press OK.

  10. Select both the emailaddress and telephone columns. Under the ‘Transform’ tab, select ‘Unpivot columns’.

  11. The default names (attribute and value) can be changed if preferred in the formula bar, located on top of the data preview. Select the name(s) of the field(s) and change them to anything that is understandable to the end user(s). For this guide attribute becomes Type and value becomes contactInformation

  12. To make sure there are no uppercase letters in the column, select the ContactInformation column. Under the same ‘Format’ button, there is the option ‘Lowercase’. This will ensure that all emailaddresses are formatted correctly.

    Use the same Format button to ‘Trim’ the field, removing any trailing whitespaces.

  13. In some situations multiple contacts have the same phone number of email address. This will not work for power BI. Please make sure to remove duplicates by right clicking on the column and selecting ‘Remove Duplicates’. Or group the query on the ContactInformation column.

  14. Press Close and Apply to load the data into Power BI and navigate to the reporting screen.

  15. Once the data has loaded, navigate to the data model tab (last tab on the left side of the screen). Locate the newly added Dynamics365 Contacts table (named in step 6 of this guide). Newly added tables are usually added on the top right of the model. You might need to scroll to the right, depending on your screen resolution.

  16. Select the table and drag it towards the ‘Contacts’ table. Release the table and select the ContactInformation field. By dragging that field onto the ‘URI’ field from the Contacts table, you will create a relation between the two tables.

    Note: If Power BI warns you for a ‘more on more’ relation, please make sure you have completed step 12 of this guide.

  17. You can now use the Fullname column in reports like the Contact Report. Just replace the contact URI field with the fullname field in the table and the data will be grouped by the CRM contacts.

    Note: In most situations not all sip/tel information can be combined right away, adding an empty row. Adding a custom field, adding the URI field when no contact is found, or refining the CRM data, might resolve this issue.