Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
The latest version of the Power BI Client still does not sort field names with queries on Dynamics CRM. This continues to make it very difficult to use as most of our CRM entities contain a lot of fields. Trying to scroll through the field names to select or de-select fields is very slow and painful. Any idea when this will change? Please note that due to our need to include custom fields, the web version (content pack), which is sorted by field name, is not an option for us. Thanks.
I signed up to discuss this very issue; I cannot discern the logic to the way the fields are currently displaying after you load the set. It doesn't appear to be by logical name, schema name or display name, nor anything like creation date as the GUID field is half-way down the list. It makes trying to create relationships between custom entities extremely tedious.
It appears that by going into the query editor you can manually change the order of fields (so finding the IDs and sending them to the beginning of the query) however those changes don't appear to carry throughout the set, as the order when viewing relationships has not changed.
The columns are displayed in the order they're listed in the metadata that comes back from CRM. We assume that a given source is returning columns in an order which makes sense for that source. I think it would be a mistake to do anything else.
You can sort columns alphabetically by adding a single formula
= Table.ReorderColumns(Source, List.Sort(Table.ColumnNames(Source), Comparer.FromCulture("", true)))
(where Source is the table you want to sort). This won't help with related tables, but it's easier than dragging a bunch of columns around. If there's a sort heuristic you want based solely on column names and column types, that's probably not hard to implement as a custom query step.
Thanks! The sorting worked - below is an example of my advanced query.
let
Source = OData.Feed("https://xxxxx.crm.dynamics.com/XRMServices/2011/OrganizationData.svc"),
OpportunitySet_table = Source{[Name="OpportunitySet",Signature="table"]}[Data],
#"Sorted Columns" = Table.ReorderColumns(OpportunitySet_table, List.Sort(Table.ColumnNames(OpportunitySet_table), Comparer.FromCulture("", true)))
in
#"Sorted Columns"