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 ) and of course I was looking for a way to get this data into Power Pivot and Excel. I found a way

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:

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