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