Import data from TFS online into the data model through Power Query

I was playing around today with some TFS data (that’s were we store all our product data Smile) and of course I was looking for a way to get this data into Power Pivot and Excel. I found a way Smile

It turns out TFS online supports OData for most of their services. See this blog post on how to enable it and where to connect to: 

The important thing is this:

Team Foundation Service authentication:

(Optional) In order to authenticate with Team Foundation Service, you will need to enable and configure basic auth credentials on

  • Navigate to the account that you want to use on For example, you may have
  • In the top-right corner, click on your account name and then select My Profile
  • Select the Credentials tab
  • Click the ‘Enable alternate credentials and set password‘ link
  • Enter a password. It is suggested that you choose a unique password here (not associated with any other accounts)
  • Click Save Changes

To authenticate against the OData service, you need to send your basic auth credentials in the following domainusername and password format:

  • accountusername
  • password
  • Note: account is from, username is from the Credentials tab under My Profile, and password is the password that you just created.

So I did that for my TFS online instance and opened up PQ and selected import from OData:


Next I pasted in my workitems OData url (that I got from the site above):


This prompts me for authentication, and I choose basic auth and enter my credentials:


This opens up the query editor and I am good to go from that point:


It’s pretty cool that this worked and I though I share it Smile

4 Replies to “Import data from TFS online into the data model through Power Query

  1. perfect. Thank you! Was just asked by some stakeholders for more reporting from VSO yesterday morning, and read this in the afternoon, perfect timing!

  2. When I try to do this (also by the browser) I get “Not found – The TFS Project Collection named DefaultCollection was not found.”


  3. It does not work for me 🙁 I got this error : “OData: The given URL neither points to an OData service or a feed:”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.