To get started, open Power BI Desktop and Dynamics 365. In Power BI, use the Dataverse connector and select the environment containing your CRM data.

A Dynamics 365 environment can contain a large number of Dataverse tables. Many are background tables used by CRM, so finding the table you need may not always be straightforward. For example, searching for "case" may not return the table containing support cases. In Dynamics 365, looking at the URL for the Cases page shows that the underlying entity is Incident. Searching for "incident" in the Dataverse connector locates the appropriate table.

This is a useful approach when the name you see in Dynamics 365 doesn't match the table name you see in Power BI.

Choosing between Import and DirectQuery

When bringing Dataverse data into Power BI, you can choose between Import and DirectQuery. Import is the traditional method of pulling data into Power BI. The data is loaded into the report so it can be transformed and used for reporting. With DirectQuery, the data itself isn't stored in the report. Instead, Power BI queries Dataverse as the report requires data. DirectQuery can be useful when you need:

• CRM data available in real time
• Access to datasets that are too large for Power BI to handle normally

However, DirectQuery also has drawbacks. Because Power BI queries Dataverse as the report changes, visuals can be slower than those using imported data. Large queries can also put additional load on the data source.

DirectQuery also limits some transformations, measures, and features. For reports that don't require real-time data, Import may therefore be the better option.

Working with relationship columns

Dataverse tables can contain a large number of columns, including relationship columns. Relationship columns connect to other Dataverse tables. They allow you to retrieve selected information from a related table without importing that entire table.

For example, if a case is linked to quote data, you can expand the relationship and select only the fields you need. This can reduce the work involved in importing an additional table, creating the relationship, and managing unnecessary columns.

Relationship columns are convenient, but having too many of them can also cause performance problems. In some situations, Power BI may report that the query is too complex. If that happens, open the Source step in Power Query, go to Advanced Options, and clear the option to include relationship columns.

Finding Dynamics 365 fields in Power BI

After loading CRM data into Power BI, you may notice that field names don't always match the names displayed in Dynamics 365. Power Apps can help you determine how those fields map to Dataverse.

To view this information, you need to be a system customizer or system admin in the environment. In Power Apps:

• Make sure you're working in the correct environment.
• Navigate to Tables.
• Open All tables.
• Find the table you need.
• Open its schema and columns.

Here you can review the columns and relationships for the table, including their display names and logical names. The logical name is the name that appears in Power BI. This makes Power Apps useful when you recognize a field in Dynamics 365 but can't locate its counterpart in Power BI.

Understanding codes and names

Another characteristic of Dynamics 365 CRM data is that many fields, particularly dropdowns and option sets, include both a code and a corresponding name. For built-in fields, Power BI will often receive both values. Some custom fields, however, may provide only the code.

When you only have the code, you can use Power Apps to inspect the field's choices and determine which labels correspond to each value. This can be especially useful when working with customized CRM environments where additional choice fields have been created.

Handling missing option-set labels

If Power BI contains an option-set code but not its corresponding name, you can retrieve the mapping from Power Apps. For example, Dynamics 365 status information can contain different status reasons based on whether a case is active, resolved, or canceled. You can view these choices in Power Apps and use them to create a small table in Power BI containing the IDs and corresponding status names. You can then relate this table to your CRM data and use the relationship to retrieve the appropriate label.

Although this may not always be necessary, it's a useful technique when working with CRM data that contains codes without their corresponding names.

Building a basic CRM report

Once the required CRM data has been loaded and the fields have been identified, you can begin building the report. A simple matrix, for example, can use:

• Customer name
• Status
• Priority
• A count of case IDs

This creates a breakdown that shows customer cases by their current status and priority.

Key takeaways

Dynamics 365 CRM data can be accessed in Power BI through the Dataverse connector using either Import or DirectQuery. When working with this data, remember:

• CRM display names don't always match underlying Dataverse table or field names.
• Dynamics 365 URLs can help identify the underlying entity you're viewing.
• Power Apps can be used to find the logical names of tables and fields.
• Relationship columns can bring selected data from related tables into a query.
• Relationship columns can also contribute to performance issues when there are too many of them.
• Option-set fields may contain both codes and names, while some custom fields may provide only the code.
• Power Apps can help identify the labels associated with option-set codes.

Once you know where to find the underlying table names, logical field names, and option-set values, Dynamics 365 CRM data becomes much easier to prepare and use in Power BI.