In this article, learn how to resolve common issues using OData for Excel. To know more about setting up OData, see the following:

Enabling OData  

Creating an OData Connection 

Using OData with Excel 

OData Excel Report Building FAQs 

Also, see the following videos:

OData Connector setup and User Profile setup (23.1) - AIQ Academy

Generate OData User Credentials (23.2) - AIQ Academy

Use OData Connector with PowerBI (23.3) - AIQ Academy

Using OData connector with Excel (23.4) - AIQ Academy

What version of Excel can I use with OData?

Excel 2010 supports OData, but Power Query needs to be downloaded as an Add-on for Excel 2010-16 versions. Excel 2016 has Power Query In-Built along with OData, but you may be prompted to enable this when first accessing Power Pivot Table.

How do I revoke user access to OData?

As an Administrator:

• Revoke access for all users to a particular company, by going to Setup > Company Details & Settings > Integration and clicking Disable the Power BI, Excel OData, and endpoint configuration.
• Revoke access to specific datasets within an entity by selecting Maintain Menu Profiles for a specific entity, selecting the OData tab on the resulting screen, and de-selecting the relevant datasets.

As a User:

• Revoke your own access (for example if you are worried that your login credentials have been compromised) by logging into the company, going to Analysis > Data Connection Setup, and clicking Revoke for the relevant password.

I have forgotten my login credentials or password

Access your OData Connection URL and Username at any time in the Data Connection Setup screen.

If you did not record your password when you generated it at setup, you will need to do so again. For more details, see Creating an OData Connection. You will be prompted to enter the new password when refreshing any existing reports after disabling the old password. For security reasons we recommend also revoking the old password as described above. 

I’ve successfully run OData but I cannot see any of the ‘F(x)’ Formula reports, only the Static Data reports like Suppliers listing, and General Ledger Reports.

Not all Excel versions/subscriptions support all OData functions. The following may not support the remainder reports:

• Excel for Mac
• Tableau
• Python Fetch commands using OData

PowerBI might be an alternative in some cases.

To check whether it is a problem with your Microsoft software, connect to this public test URL via Excel:

You should see the following sample list of function datasets if your software version / subscription supports them:

I’ve set up my connection table. Query looks like it is loading or is stuck on loading, but nothing is loading in the Queries and Connections screen.

In the Queries and Connections screen, right-click on the selected table. Select the Load To option and re-select the options you want to add to your Data Model. This should restart the process, and the table should now load.

What is the difference between downloading the data as an Excel Table vs as a Connection?

Excel

With Excel, typical constraints apply and if dealing with a large volume of data, updating may take some bandwidth each time. Excel row limit 2007 onwards: 1,048,576 Rows,16,384 Columns

Connection Only

With Connection only, the data is retrieved via the connection allowing for higher volume of data and speedier loading. Connection Row Limit Excel 2013 onwards: Tens of millions of rows and columns, technically, but limited by system performance, data source etc., as opposed to an Excel limitation.

Can I have multiple Power Pivot Tables per Excel Worksheet? 

Yes, though you will need to be careful where to position an additional table, as it cannot interfere with the existing pivot table layout. For instance, it can be put beside an existing table to create two Pivot Tables, one for P&L MM and YTD and another showing All Periods. If it is added below an existing Pivot Table, the system will warn if there is a danger of overlapping an existing table via filters added etc.

How can I get help building reports?

The data feeds can be used to build simple data extracts. 

They can also be used to build powerful reports similar to the provided Power BI report template. For this type of complex reporting, you will need report-building skills. There are many resources that can guide you such as Microsoft's Power BI YouTube channel, Udemy, and Coursera. In addition, refer to {link}.