Retrieving Data from Project Online with Excel 2016

Working with the products that are offered through the subscription method used by Microsoft Office 365 brings many advantages for companies and individuals. People can work from almost anywhere, can collaborate with co-workers and team members, and can also have the most recent versions of the software installed on their machines – so when a new feature is released they can have access to it right-away.

However – and it seems that there is always a ‘however’ – there are some circumstances in which working with a subscription product can be very tricky, especially when existing functionalities and features are moved to other places or when they are simply removed from the software.

This happened to me a few days ago, when I was trying to create a new report for a customer in Microsoft Excel. I am using Microsoft Excel 2016, and have updated the software to get the latest updates as of the time I’m writing this (version 16.0.8326.2076). In the previous version, retrieving data from Project Online’s OData schema was easy: you just had to navigate to the Ribbon and then click Data > From Other Sources > From OData Data Feed:

Figure 1: Retrieving data from Project Online

To my surprise, after I updated my machine, the Data section of the Ribbon had been changed completely:

Figure 2: New Data tab in the Ribbon

It seems to me that with this change Microsoft is moving their preferred method for getting data to the Power Query wizard (through the Get & Transform Data group). The problem with this is that, so far, reports that use Power Query cannot be refreshed from within the browser in Office 365, which can present a problem for companies that have their reports created in Microsoft Excel and consumed online.

So, the question here would be: how can we create reports in Microsoft Excel that do not rely in Power Query and, more importantly, will refresh from within the browser? The good news is that even though the Data tab of the Ribbon has been changed, Microsoft Excel still allows you to use the old way for retrieving data through its wizards – called ‘legacy’.

If you are suffering from the same problem, you simply need to configure your Ribbon to include the legacy for the OData wizard. Here are the steps:

1. Open Microsoft Excel
2. Navigate to File > Options
3. Select Customize Ribbon
4. In the window, change the option in the ‘Choose commands from’ from Popular commands to All commands.
5. Then, in the list of the available commands, search for the command From OData Data Feed (Legacy).
6. To the right of the window, you will see all the existing tabs that are available in the Ribbon. Click New Tab and rename it. You can also organize your new tab into groups, if desired.
7. After that, use the Add>> button to move the command to the new tab. (You can also add other commands to the new tab if needed.)
8. After finishing the configuration, click OK

Figure 3: Customizing a new tab in the Ribbon

When you go back to Microsoft Excel, you will see the new tab with its commands:

Figure 4: The new ‘Get Data’ tab

Now you can use the new tab to retrieve the data from Project Online and build your report:

Figure 5: Data Connection Wizard

Figure 6: Report with data from Project Online

Be aware that if you need to make any changes to your data connection, you can navigate to the Data tab in the Ribbon and click Queries & Connections to see the Queries & Connections pane to the right of the screen (or you can add this command to your custom tab):

Figure 7: Queries & Connections panel