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...
StNick's avatar
StNick
Regular Visitor
11 years ago

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.

  • 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"