Connect Power BI Template to Dynamics365

Enhancing the data from Anywhere365 with information from Dynamics365.

Prerequisites

  • Have a Power BI file with the Anywhere365 view loaded in (using the template for example)

 

Quick Setup

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 (change the Dynamics365 API to your own Dynamics365 API)

let

Source = OData.Feed("Dynamics365 API"),

ContactSet_table = Source{[Name="ContactSet",Signature="table"]}[Data],

#"Removed Other Columns" = Table.SelectColumns(ContactSet_table,{"Company", "FullName", "JobTitle", "MobilePhone", "Telephone1", "Telephone2", "Telephone3"}),

#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Telephone1] <> null)),

#"Removed Blank Rows" = Table.SelectRows(#"Filtered Rows", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),

#"Replaced Value" = Table.ReplaceValue(#"Removed Blank Rows","-","",Replacer.ReplaceText,{"Telephone1"}),

#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," ","",Replacer.ReplaceText,{"Telephone1"}),

#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","(","",Replacer.ReplaceText,{"Telephone1"}),

#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2",")","",Replacer.ReplaceText,{"Telephone1"}),

#"Removed Duplicates" = Table.Distinct(#"Replaced Value3", {"Telephone1"})

in

#"Removed Duplicates"

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

  1. If this is your first time connection, enter your credentials
  2. Click Done
  3. (optional) Rename Name
  4. Click Close & Apply
  5. Open tab Data and navigate to the created table
  6. Add the following column through the three dots right of the table name: PhoneJoin = "tel:" & tablename[Telephone1]
  7. Open tab Relations
  8. Connect PowerBI_Dialogue.caller with Query1.PhoneJoin