Forum Discussion
Importing Specific Columns from Dynamics 365 with Native Query and Structured Columns
Hi A_Yao ,
Yeah, this is a common issue when trying to pull only specific data from Dynamics 365 into Power BI, especially when it comes to those structured columns like lookups or related records. When you use a native SQL query through the TDS endpoint, it doesn’t actually support those complex fields—that’s why they just don’t show up. It’s a limitation of how the SQL connector works with Dataverse.
If you want to keep your dataset lean and still access structured data, you might want to try connecting via the OData feed instead of using SQL. With OData, you can use $select to grab only the fields you need, and $expand to include data from structured columns—so you're not pulling down everything, just what’s relevant.
Here’s how I usually do it: use Get Data > OData Feed in Power BI, enter the URL like https://<yourorg>.crm.dynamics.com/api/data/v9.1/, and then from there, you can pick your table and expand only the fields you care about. You can even filter right in the URL to cut down the volume even more, like filtering by date or status, etc.
If you really have to stick with SQL, one workaround is to create a custom view or calculated column in Dataverse that flattens the structured data into regular fields, then expose that through the TDS endpoint. But honestly, OData is a lot more flexible for this kind of scenario.
To address the issue of connecting a Power BI dashboard to Dynamics 365 while minimizing data usage and ensuring structured columns are accessible, follow the steps below:
1. Understand the nature of structured columns in Dynamics 365. These often refer to lookup fields, option sets, or navigation properties which are stored as complex types in the backend and not exposed directly via SQL.
2. Native SQL queries in Power BI for Dataverse (used by Dynamics 365) are limited in that they do not support complex or structured columns. This is a limitation of the TDS (Tabular Data Stream) endpoint, which only exposes a flattened view of the data.
3. To retrieve structured column data efficiently without downloading full tables:
- Use Power Query with OData feed instead of Native SQL. The OData feed allows you to selectively expand structured fields.
- Go to Power BI > Get Data > OData Feed.
- Enter the URL in the format: https://<yourorg>.crm.dynamics.com/api/data/v9.1/
- Select the desired table (entity) and then expand only the specific fields from structured columns that you need, such as ‘_ownerid_value’, ‘parentcustomerid’, etc.
4. In the Power Query Editor:
- Load the minimal base table.
- Identify and expand only the required structured fields by clicking the expand icon.
- Filter out unwanted columns early in the query steps to minimize memory usage and processing time.
5. To further reduce data usage:
- Apply filters using the OData query options in the URL itself (e.g., $select and $filter).
- Example: https://<yourorg>.crm.dynamics.com/api/data/v9.1/accounts?$select=name,revenue&$filter=revenue gt 1000000
6. Avoid using the full dataset download by managing load options:
- In Power Query, right-click unnecessary queries and disable load.
- Use query folding wherever possible to push data filtering to the server.
7. If structured fields must be queried in SQL and are essential, consider denormalizing the data by pre-building a view in Dataverse using Power Platform’s custom tables and calculated fields, then expose this via the TDS endpoint.
If this solution helps you resolve the issue, please like and accept the solution.