Industry and services
The ideal digital transformation partner is not only an expert in theory and technology but also in its customer’s industry. Our consultants are familiar with the processes, priorities and challenges of these eight focus industries.
Contact us
Manufacturing companies
proMX has long been focused on supporting manufacturing companies with their digital transformation.
proMX services
proMX helps you tackle challenges by analyzing your processes and customizing your system with tailored solutions. In our discovery workshop, we'll pinpoint the right tools to boost productivity. Let’s get started!
This might be useful
Partners
Partnering with proMX Group to offer the proMX 365 Productivity Suite provides a unique opportunity to enhance your business portfolio and drive significant growth.
Become a Partner
Our mission
All companies strive to digitalize and optimize their processes, and thus become more productive.
About us
proMX is your digitalization partner. Our goal is to help you transform your processes to make your business more agile, efficient and competitive. As a Microsoft Partner we are both extremely experienced and well-connected.
More about proMX
Our mission
All companies strive to digitalize and optimize their processes, and thus become more productive.
Nils Pelzer
Sep 28, 2016 | Last updated: Dec 16, 2022
Expert articles | 3 min read

Recently, we were tasked with migrating a customer’s Power BI report for CRM 2016. Although the old OrganizationData.svc still works as a data source, we decided to use the new OData v4 service of CRM 2016 Online. We also looked into how to further optimize the performance when updating the data model. This is especially important for reports with a dynamic data source. As time passes, your data source grows and it consequently takes longer to update your report. An update that used to take just a few minutes, may very well take a couple of hours a few months on.

Unfortunately, Power BI isn’t very clever if you design your queries with the query editor’s GUI. Instead, connect to CRM Online and select an entity.

You will get a notification to reduce the number of columns in order to improve performance:

img-four

After selecting the columns you need for your report, you end up with a table that contains only those columns. But if you take a look at the actual query in the Advanced Editor, you can see that you always start with a table that contains every column. In the next step, you remove every column that you didn’t select.

let
 Source =
 OData.Feed("https://<tenant>.api.crm.dynamics.com/XRMServices/2011/OrganizationData.svc/Entity")

#"Removed Other Columns" = Table.SelectColumns(Source,{"Column"})

in

#"Removed Other Columns"

Thankfully, it is possible to use a filter in your OData source query using the Advanced Editor. Modify your query accordingly:

let

  Source =
 OData.Feed("https://<tenant>.api.crm.dynamics.com/XRMServices/2011/OrganizationData.svc/Entity?$select=Column")

in

  Source

The result of both queries is exactly the same, except that the latter is potentially a lot faster than the first.

Unfortunately, filtering the initial OData query doesn’t work as expected if you are using the new OData service of CRM 2016 Online.

let

  Source =
 OData.Feed("https://<tenant>.api.crm.dynamics.com/api/data/v8.0/Entity?$select=Column")

in

  Source

This query results in a table that contains the data of every column that you selected, and additionally every other existing column without any data. We opened a Support Call for this issue with the Power BI team, and they confirmed that this is a bug in Power BI v. 2.31.4280.661. However, modifying the source query and filtering for the required columns is still a lot faster as the returned columns contain no data.

Until this bug is fixed, the Power BI team recommends using the following syntax to start your queries:

let

  Source = 
OData.Feed("https://<tenant>.api.crm.dynamics.com/api/data/v8.0/ Entity?$select=Column"),

#"Removed Other Columns" = Table.SelectColumns(Source,{"Column"})

in

#"Removed Other Columns"

Answering