THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

BI Beginner: Power Query and OData

I’ve observed that not very many data professionals are familiar with OData. It’s an open data access protocol built on AtomPub and JSON. It provides a RESTful means of retrieving data, which is what this post is about.

One of the things that makes OData important is that it is recommended by the Open Government Data Initiative. Although this post uses the familiar SQL Server AdventureWorks database, remember, OData is open. It’s not limited to SQL Server or even SQL databases in general.

A small subset of AdventureWorks is published at


Figure 1. AdventureWorks subset published at

Open Excel that has the Power Query add-in installed and select the POWER QUERY tab. Select From Other Sources and then choose From OData Feed.


Figure 2. Selecting From OData Feed.

Enter the URL for the AdventureWorks OData source and click OK.


Figure 3. Specifying an OData source.

Notice that the Power Query Navigator has a popup for peeking at the data.


Figure 4. Peeking at the data.

Double-click CompanySales to open the Query Editor. You can rename and delete columns as well as several other changes you might want to check out.


Figure 5. Query Editor

After clicking Apply & Close, the data was loaded into the spreadsheet.


Figure 6. CompanySales from OData loaded into Excel.

Notice how the query indicates when it was last updated. It’s important to understand this isn’t just a static one time download. This is a data model in Excel that is connected to a live data source. Go to the DATA tab and select Refresh All.


Figure 7. Notice that the last updated time has changed to reflect the update.

By taking advantage of new data management features in Excel, you’re no longer stuck with static, stale spreadsheets.

Published Saturday, March 29, 2014 1:21 AM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Rafi said:

Yes, that is true, but the key here is how do I create a ODATA publications of my own data? How do I limit who can access the data I expose with OData?

March 31, 2014 6:22 AM

Ian Yates said:


ASP.Net WebAPI OData...  It demos really well for simple models where you just want to take a database (with Entity Framework layer) and just expose it.  

However it can a bit of a learning curve if you want to really customise your service.  Very powerful but be prepared to throw away your code and try various approaches.  It's come a long way over the past 18 months and is now MS' preferred way of exposing OData as the old WCF data services won't be updated to support OData v4.

April 1, 2014 10:06 PM

Leave a Comment


About John Paul Cook

John Paul Cook is a database and Azure specialist in Houston. He previously worked as a Data Platform Solution Architect in Microsoft's Houston office. Prior to joining Microsoft, he was a SQL Server MVP. He is experienced in SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse currently studying to be a psychiatric nurse practitioner. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Connect on LinkedIn

This Blog



Privacy Statement