Best practice to avoid expanding columns when connecting to Dynamics CRM?

[psbuser18]

I understand that you should avoid expanding columns from an entity to avoid a slow report when refreshing data from Dynamics CRM.

How can I find the related table that the expanded column is from in Power BI as the advanced editor does not give the related table name. Importantly, how can I locate the embedded columns entity to avoid having to expand the column in power query?

Can you then confirm the best practice for avoiding expanding columns in Power BI, is it simply to load the related tables back to the data model and then perform a lookup to bring the data across rather than expanding the embedded ID column?

[edhans]

I'm not sure what you are referring to as it being something to avoid. Expanded columns in that context are just merged columns from a joined table, and that is what they are there for. You just don't have to do the merge manually in the Power Query editor. But you should only expand the columns you need. If there are 50 columns, and you need 3, just check off those 3 before expanding.

You definitely do NOT want to load a bunch of unnecessary tables in to the model just for lookups. That will perform much worse for your users and that all happens real-time while they are clicking and filtering the report, whereas doing it in Power Query might result in a longer refresh, but once loaded, DAX has to do very little work, which is better for the end user.

[psbuser18]

I am not referring to expanding columns for a merge, rather expanding columns that are already embedded to the entity, (these appear in yellow and each row appears as record). For example if I am looking at the account entity and owning team is a column of that entity that needs to be expanded to view the ID column.

This is a major performance hit on the report and I am looking for a solution to get the ID column without expanding the column and taking the performance hit, if there is another way when working with Dynamics CRM?