Steps to import into Microsoft Excel

With Microsoft Excel and Microsoft Power BI you can easily import the data using the OData-feed import wizard.

1. First, make sure you read the before you start section.
2. In Excel go to Data -> Get Data (or New Query in older versions) -> From Other Services -> From OData Feed
   
   
   
3. Enter the odata URL (see first step) and click OK.
   
   
   
4. A new window will popup. Select the option Basic and enter you API Credentials.
   Enter the Client Id as user name and Client Secret as password.
   
   
   
5. When your credentials are valid a new window will popup. In this window you can select the data you would like to read.
   You can start by selecting one table/source at the time. When you want to add more tables later on you just need to follow these steps again. After you've selected a table/source and clicked Load, the data will be imported in a new sheet.
   
   
   
6. When the data is imported in the sheet, some columns still need 'expanding' before you can see all the data in Excel. At the right hand side you right click on the table/source and click Edit.
   
   
   
7. In the next window you see all the columns. Some columns have a special 'expanding' button in the column header. Click on that button so select the sub-fields you would like to see. When you are done click close & load at the top left.
   
   
8. You can now store this Excel sheet. When you want to refresh the data just hit Data -> Refresh All.

Click here for some more usefull information.