New Features in the Power Query Dataverse Connector!
We are excited to feature two new capabilities for our Dataverse connector – environment discovery, and support for native database queries in the Power Query Advanced Editor. With these changes, connecting to Dataverse is both easier and allows more advanced querying capabilities. The Dataverse connector is currently available within Power BI Desktop and will soon become also available in Power Query in Excel and in Power Query Online in Dataflows experiences.
Finding Dataverse environments has become much easier with the October 2021 release of Power BI Desktop. Users will now be able to view a list of Dataverse environments they have access to and select one to connect to. There is no longer a need to find and copy/paste the environment URL to access the environments.
Native Database Queries with the Advanced Editor
In addition, for more advanced users, support for native database queries is now available for the Dataverse connector. This allows report authors to write custom SQL statements which can be optimized for a specific KPI or leverage key functions like CURRENT_USER. While there’s no user interface for this experience, you can enter the query in M using the Power Query Advanced Editor. In order to use a native database query, an Environment must be specified as the Source.
Instructions on how to use this feature can be found in the Dataverse connector documentation. Here is a sample query.
First, define the source with the environment URL.
Source = CommonDataService.Database([ENVIRONMENT URL])
Once a database source has been defined, you can specify a native query using the Value.NativeQuery function.
myQuery = Value.NativeQuery(Source, [QUERY], null, [EnableFolding=true])
Altogether, the query will look like this.
We are super excited to hear your feedback after using this connector! Feel free to leave a comment below with your thoughts.