Importing Specific Columns from Dynamics 365 with Native Query and Structured Columns

[A_Yao]

I have built a dashboard that I am trying to connect directly to Dynamics 365 in order to draw data automatically from, and to that end I am trying to cut down on the total data usage to only the necessary columns that I need.

I am currently using Native Query with SQL to do so, as it allows me to specify which columns I want before the download occurs, but the dataset that I am getting from Dynamics includes structured columns, which for some reason are completely absent and uninteractable when using Native Query; for example, using SELECT * FROM returns all columns except the structured columns.

I could use the use the command to expand the structured column that is built into Power BI, but that would require downloading the entire dataset first, which I am trying to avoid as it is quite the large amount to be refreshing often. Yet if I use Native Query to only get the columns I want, the important info in the structured columns are unavailable.

Is there any way for me to be able to achieve these two goals simultaneously?

[rohit1991 · Accepted solution]

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.

[v-dineshya]

Hi @A_Yao ,

Thank you for reaching out to the Microsoft Community Forum.

Hi @Sandip_Palit , @pankajnamekar25 , Thank you for your prompt response.

Hi @A_Yao , If @Sandip_Palit , @pankajnamekar25  response has resolved your issue, Please mark their response as “Accept it as a solution”  to assist other community members in resolving similar issues more efficiently.  And, if you have any further query do let us know.

Regards,

Dinesh

[pankajnamekar25]

Hello @A_Yao 

try these options

Use OData URL expansions ($expand) instead of native SQL to retrieve specific fields from structured columns directly from the server.

 If using SQL, create a custom view or intermediate entity in Dataverse to flatten and expose required fields.

 In Power BI, expand structured columns only after applying table filters, to minimize data volume.

Use Power Automate or Azure Data Factory to extract and preprocess structured data into a flat table.

Consider using Dataflows with OData and $select + $expand to control data before loading to Power BI.

Thanks

 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[Sandip_Palit]

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.