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: https://tfsodata.visualstudio.com/ 

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 tfs.visualstudio.com:

  • Navigate to the account that you want to use on https://tfs.visualstudio.com. For example, you may have https://account.visualstudio.com.
  • 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 account.visualstudio.com, 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:

image

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

image

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

image

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

image

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.”

    Idea?

  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.