Microsoft Power BI Desktop is a popular business analytics tool. It supports over 250 native connectors to popular data sources like SQL Server, Excel, Salesforce, Google Analytics, SharePoint and more. In this blogpost you will learn how to connect to Microsoft Power BI to Microsoft Dataverse. I will also provide resolution to common issues faced.
What is Dataverse
Dataverse is a core component of Microsoft Power Platform. Through Dataverse users can securely store and manage data that is generated and accessed by business applications created on Power Platform. It is being used by Microsoft in its own Dynamics 365 business applications such as Sales, Customer Insights, Customer Service and more. Data is stored in the form of tables and has features like calculated columns, business rules, workflows etc. Application developers can quickly build multiple applications using the same Dataverse tables.
Power BI Desktop and Dataverse Integration
Power BI has built in connectors to connect to the Microsoft Dataverse. This helps report builders to easily get the desired data and create reports that derive business decisions. These reports can also be embedded back into Power Apps or Microsoft Dynamics 365 applications. Depending on your requirement and experience in Power BI Desktop, you will connect to Microsoft Dataverse in two ways.
Completely using GUI: With this method you will use different options available in Power BI Desktop or Power Query to get data, apply transformation and create a report with very less code written.
Using SQL: With this method most of your logic will be covered in a SQL statement in Power Query’s M code. Few steps which are not supported through SQL in Dataverse can be covered through Power Query’s transformation steps.
Connect to Dataverse using Power BI Desktop
In Power BI Desktop click on the Dataverse icon in the Home ribbon’s data section.
It will ask you to log into your organizational account. Use an account which has access rights to the Power Platform environment. In case your Power BI account already has access to the Power Platform environment, you can also use it. Once signed in, click connect.
A window will appear containing a list of Power Platform environments. Expand the desired environment and select the tables you want to work with, and click on Load or Transform button. Power BI Desktop will ask you to select connectivity mode as Import or Direct Query.
Connect to Dataverse using Power Query
Using the Power Query interface, select New Source > More > Power Platform > Dataverse. Then the same steps will appear to sign into organization and selecting the required tables.
Connect to Dataverse using Power Query and SQL
You can use SQL statement to query data from Dataverse and apply some transformation. Once you have imported a table from Dataverse, click on Advance Editor. The structure of your M code will be similar to the following in which I have selected account table from Dataverse. 1st line contains URL of your Power Platform environment.
Replace the 3rd line of code as follow. Use Value.NativeQuery function with souce and SQL query as input parameters.
With SQL statements you can select specific columns, give them aliases, filter data at source, join tables with others and add new columns with custom logic. Maintenance of code becomes very easy.
Common Issues while connecting Power BI Desktop to Microsoft Dataverse
Could not authenticate with credentials provided
This issue happens when the organizational account used to sign into Dataverse does not have access rights to Power Platform environment. To get rid of this issue, grant this account access to Power Platform environment or use another account which already has necessary access.
Disabled TDS Endpoint
This issue happens when TDS endpoint setting is disabled in Power Platform environment. Contact your Power Platform Admin and ask him to enable the following TDS endpoint setting for the Power Platform environment. It can takes few minutes for the settings to be reflected.
User misses prvAllowTDSAccess privilege
This issue happens when although TDS endpoint is enabled but another setting Enable user level access control for TDS endpoint is enabled in Power Platform environment. This setting enable granular access control and only people allowed by Power Platform admin can use TDS endpoint to connect to Dataverse from Power BI Desktop.
Ask your Power Platform admin to add security role having Allow user to access TDS endpoint privilege to your user account.
This issue can appear when changing credentials in Power BI Service to an account with insufficient permissions for TDS endpoint.