Configuring Power Query OData Feeds

Scenario: A customer has implemented a Power BI model that retrieves data by calling the Dynamics CRM OData Feed endpoint. The dataset refresh operation is timing out. They want to increase the timeout setting but unlike connecting to SQL Server, where you can set the timeout duration in the source advanced properties, there is no UI for ODATA.

Solution: If you open the query behind the table, and click the Source step, you’ll see in the Query Editor formula bar the following M code:

= OData.Feed(“https://<tenant>.api.crm.dynamics.com/api/data/v8.1/”)

From the documentation, we realize that OData.Feed can take additional settings and one of them is the Timeout setting that you can pass to the third options argument. For example, you can change the M code to set the timeout to 1 hour:

= OData.Feed(“https://<tenant>.api.crm.dynamics.com/api/data/v8.1/”, null, [Timeout=#duration(0,1,0,0)])

The timeout uses the duration data type which is explained here. Bringing this one step further, you can restrict what records get pulled in the OData.Feed using $filter and also only grab data for the columns you’re using by including the field names in the $select parameters, e.g.

“https://<tenant>.crm.dynamics.com/api/data/v8.1/leads?$filter=<somefield> eq 740110001&$select= <removed list of fields>”