If you’re working within the Power Platform, you’ve likely encountered the need to connect Dataverse to Power BI. While Power BI offers a built-in connector for Dataverse, it doesn’t always provide the best experience especially when dealing with complex system tables. In this post, you’ll learn two powerful methods to connect Dataverse to Power BI: using the standard connector and the more efficient TDS SQL endpoint.
What Is Dataverse and Why Use It with Power BI
Dataverse is the relational storage engine used across the Power Platform supporting predefined and custom tables (entities). It enables structured data storage and management. While Power BI’s native connector integrates seamlessly with Dataverse, connecting to system tables like systemuser sometimes leads to errors due to complex column types like polymorphic lookups or large column counts.
Issue: Preview Errors with Native Connector
When using the Power BI Dataverse connector, you may encounter issues loading certain tables especially system tables that include hundreds of columns. In file preview mode, Power BI might produce a “preview failed” error because of unsupported data types or oversized schemas. This blocks analytic workflows because the interface doesn’t allow you to exclude problematic columns.
Workaround: Manually Selecting Columns in Power Query
To bypass errors:
- Launch Power Query Editor.
- Open the Advanced Editor.
- Add a
Table.SelectColumnsstep to specify only the needed fields.
Example:
#"Selected Columns" = Table.SelectColumns(#"PreviousStep", {"systemuserid", "fullname"})
This explicitly narrows the schema, allowing Power BI to load the table successfully. The downside is manually typing field names and case sensitivity considerations.
Solution: Enable TDS/SQL Endpoint for Improved Access
A more robust approach is using the TDS endpoint to query Dataverse like a SQL database:
- In the Power Platform Admin Center, navigate to the environment settings.
- Enable the TDS endpoint under Features.
- Grant user access as needed (read-only).
With TDS enabled, you can connect using Power BI’s SQL Server connector or SQL Server Management Studio (SSMS) with Microsoft Entra (Azure AD) authentication.
Benefits of the TDS SQL Approach
- Fast and efficient: Query only required columns using regular
SELECTstatements. - Optimized performance: Avoid UI preview limitations.
- Greater control and clarity over data.
- Read-only access ensures safe querying.
Sample SQL:
SELECT systemuserid, fullname
FROM systemuser
You can join additional tables like team, team_membership, and securityrole to build richer dimensional insights and load directly into Power BI for efficient reporting.
Comparison: Dataverse Connector vs TDS SQL Query
There are multiple ways to connect Dataverse to Power BI, but not all are equally effective. For basic use cases, the built-in Dataverse connector is sufficient. However, if you’re dealing with large or system-generated tables, it’s more efficient to connect Dataverse to Power BI via the TDS SQL endpoint. This method offers better performance, allows you to write SQL queries directly, and gives you full control over the data you bring into your reports.
| Feature | Dataverse Connector | TDS SQL Endpoint |
|---|---|---|
| Setup Complexity | Easy, GUI-based | Requires SQL knowledge and environment settings |
| Column Selection Flexibility | Limited, may cause preview errors | Full control over queried columns |
| Performance | Slower with large system tables | Faster querying via SQL |
| Handling Complex Data Types | Can fail on polymorphic or lookup types | Handled via curated SQL logic |
| Security | Controlled via connector permissions | Read-only with admin-configured access |
Final Thoughts and Best Practices
- Use the native Dataverse connector if working with simple, custom tables.
- Switch to SQL querying via TDS for complex or large system tables.
- Always specify only necessary columns, whether via Power Query or SQL.
- Confirm that TDS is enabled and properly configured in your environment.
Learn More via Training
Looking to master Power BI, Dataverse, T-SQL, and advanced data modeling? Our expert training at Data Bear Power BI Training offers hands-on courses and virtual mentoring to accelerate your skill development.
