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.