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

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

    • Kasper de Jonge

      I live to serve, great timing 🙂

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

    Idea?

  • Viktoria M

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