Forum Discussion

dglsmason's avatar
dglsmason
Icon for Advocate I rankAdvocate I
11 years ago

Power BI Client Lack of Sorting Dynamics CRM Field Names

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 fi...
curth's avatar
curth
Icon for Power BI Team rankPower BI Team
11 years ago

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.

dglsmason's avatar
dglsmason
Icon for Advocate I rankAdvocate I
11 years ago

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"