Connecting Excel and Power BI to MobiCRM (OData)
Overview
MobiCRM provides an OData feed that lets you load your MobiCRM data into Microsoft Excel and Power BI, to build your own tables and reports.
The feed is read-only: it cannot change MobiCRM data. It exposes accounts, contacts, businesses, the products of the businesses, leads, appointments, tasks, minutes and the monthly turnovers of the accounts.
Feed address: https://odata.mobicrm.app/odata/v1
Prerequisites
- An access token to the MobiCRM API. Creating one is subject to a permission: contact your MobiCRM administrator if the "API Access Token" section does not appear in your profile.
- The read permissions on the data to load: accounts, contacts, businesses, leads, appointments, tasks, minutes and the monthly turnovers of the accounts.
The data returned is the data you see in MobiCRM:
- only the accounts, contacts, businesses, leads, appointments, tasks, minutes and turnovers in your scope are returned (accounts you manage, portfolios, teams you manage, appointments and tasks assigned to you…), as in the application;
- if your user has the global view, all the data is returned;
- deleted data is never returned;
- without the read permission on a type of data, it is not returned, including in the related tables.
Creating the access token
- In MobiCRM, open "My profile" by clicking on your photo.

- In the "API Access Token" section, click +.

- In the "Access Token Creation" window, fill in:
- Application Name: for example
excelorpowerbi; - Validity Duration: the period during which the token can be used to connect.
Then click Generate.

Warning: the token will not be displayed again. Copy it and keep it in a secure place, outside MobiCRM. The token gives access to your data: do not share it.
To revoke the access, delete the token in the "API Access Token" section of your profile.
Connecting from Excel
- In Excel, open the Data tab, then Get Data > From Other Sources > From OData Feed.
- Enter the address
https://odata.mobicrm.app/odata/v1, then click OK. - Choose the Basic authentication:
- User name: enter any text, for example
mobicrm; - Password: paste your access token.
- User name: enter any text, for example
- In the navigator, select the tables to load (for example
Accounts), then click Load.
To update the data, use Data > Refresh All.
Connecting from Power BI
Power BI Desktop
- Click Get data > OData feed.
- Enter the address
https://odata.mobicrm.app/odata/v1, then click OK. - Choose the Basic authentication, with any user name and your access token as password.
- Select the tables to load, for example
Accounts,Contacts,BusinessesandBusinessProducts.
The tables are linked by their identifiers: Contacts.AccountId, Businesses.AccountId, BusinessProducts.BusinessId, Leads.AccountId, Leads.ContactId, Appointments.AccountId, Appointments.BusinessId, Todos.AccountId, Todos.BusinessId, Todos.ContactId, Minutes.AccountId, Minutes.BusinessId, Minutes.ContactId, Minutes.AppointmentId and AccountTurnovers.AccountId match the Id column of the related table. You can create these relationships in the Model view of Power BI.
Power BI service
After publishing the report, enter the same credentials (basic authentication, token as password) in the data source settings of the semantic model to schedule the refresh. No gateway is needed: the feed is reachable on the Internet.
When the token expires or is deleted, the refresh fails: create a new token and update the credentials.
Available data
| Table | Content |
|---|---|
Accounts | Accounts: company name, type, state, category, sector, size, origin, potential, revenue, SIRET, SIREN, VAT number, APE code, account manager, parent account, creation and modification dates and authors, addresses, custom fields. |
Contacts | Contacts: title, last name, first name, position, birth date, account, contact manager, social networks, consents, emails, phones, custom fields. |
Businesses | Businesses: name, account, state, amount, estimated amount, type, origin, business manager, signing, winning, deadline and proposal sending dates, confidence index, contacts, products, custom fields. |
BusinessProducts | Products of the businesses: business, product, reference, product line, quantity, unit price, amount, margin, subscription information. |
Leads | Leads: title, last name, first name, company, position, phone, email, state, origin, manager, account, contact, creation and modification dates and authors. |
Appointments | Appointments: title, comment, type, nature, dates, time zone, address, phone, video conference link, owner, account, business, contacts, number of minutes, recurrence (series are returned as stored, without generating the occurrences), creation and modification dates and authors. |
Todos | Tasks: detail, state, type, importance, urgency, deadline, completion date, owner, account, business, contact, creation and modification dates and authors. |
Minutes | Minutes (meeting reports): title, date, importance, notes, AI summary, template, account, business, contact, appointment, points discussed, custom fields, next appointments, tasks, creation and modification dates and authors. |
CustomFields | Definitions of the custom fields of the accounts, contacts and businesses: label, type, category, list values. |
AccountTurnovers | Monthly turnovers of the accounts: account, year, month, amount, margin, quantity, manager, product line, category, creation and modification dates. |
The labels of the reference data (state, category, sector, type, origin…) and the names of the managers are provided directly in each table. Each row also contains an OpenUrl link to the corresponding record in MobiCRM.
The custom fields of an account, a contact or a business are in its CustomValues column, with one row per filled field: CustomFieldLabel gives the name of the field and DisplayValue its readable value (list labels, user names). To get one column per field in Power BI, expand CustomValues, then use Transform > Pivot Column on CustomFieldLabel with DisplayValue as values. Only the fields of the categories open to your sales teams are returned, as in the application.
Amounts are decimal numbers: their sums in Excel and Power BI are exact.
Limits
- The data is sent in pages of 1,000 rows. Excel and Power BI load all the pages automatically.
- The number of requests is limited to 600 per minute and per user. Beyond that, the feed temporarily answers "too many requests"; the next refresh works normally.
- The feed is read-only.
