Power Query connects Excel to TeamDesk

On May, Microsoft extended Power BI with new feature: an ability to use custom connectors to get the data from third-party services. Shortly after we wrote the connector for TeamDesk using Power Query language.

There are rumors, Microsoft wants to extend this tech to variety of their products (including Excel) but feature release date is not yet announced. But it turns out you do not have to wait and you can use the data from TeamDesk in Excel right now.

Prerequisites: Excel for Windows. v2016 comes with Power Query Add-in preinstalled. For Excel 2010 and Excel 2013 you can download add-in from Microsoft’s site. Unfortunately at the moment Power Query is neither supported on Office for Mac nor on Office365.

Add Power Query script to workbook

TLDR; I want to try it right now!

Add new Power Query using Data | Get Data | From Other Sources | Blank Query

In a query editor open “Advanced Editor”

Now, open this page, select all code, copy it and paste into advanced editor window.

Click Done. Now we should have new query listed. Rename it to, say “TeamDesk-Database” – name does not matter.

Now, duplicate the query, rename the copy to, say “TeamDesk-SelectView”…

…and delete last step.

Repeat once again – duplication TeamDesk-SelectView, rename the copy to, say TeamDesk-Select and delete last step. Close Query editor saving changes.

One time setup

[Download script-enabled workbook]

Now you have Power Script-extended workbook. If there is a security warning about external data connections disabled, please enable.

There are three function at your service.

• TeamDesk-Database – prompts for URL of your database, let you select a table and a view from the list, gets the data from the view.
• TeamDesk-SelectView – prompts for URL of your database, a table name in singular form, a view name and adds an ability to provide filter expression that will be evaluated in addition to view’s own filter.
• TeamDesk-Select – this is a “raw query” version for even more flexibility. It prompts for URL, table, list of columns to retrieve, a filter and sort order.

Let’s get some data using first function.

Paste URL of your database

On authorization prompt, select Basic authorization type, type your e-mail and password to access TeamDesk. Excel stores credentials in encrypted form on your local machine, and applies them to all worksheets. So, in Select which level… box you can either select …/api/v2/ level to connect to all of your databases using the same credentials or select one level below, with the number (as on screenshot), to store credentials just for this database.

And now, there is final step setup to complete. Let’s set privacy level to Public.

Connecting to TeamDesk database

Now, upon clicking Save, Power Query will prompt you to select a table and a view. Lists on a screenshots display tables and views from Project Management All-In-One sample database.

Click on a word “Table” to proceed to a list of views:

Then again, on a word “Table” to get the data from the view.

Now, Power Query will display preview of your data. Click close and load to insert the results to worksheet.

The data inserted is not a result of one-time query. Excel keeps connection to TeamDesk database and reloads data if needed. I added new project and task and refreshed the query.

And here I got new task back

In addition to manual refresh, you can set the worksheet to refresh the data after certain time interval.

SelectView function

Let’s have a quick tour for other two functions. Here are SelectView parameters:

As you can see on screenshot, here we are getting the data from Task/List Changed, just like with Database function, but provide additional filter to limit results to the tasks from Project C.

Select function

Finally, Select function allows you to specify explicitly the list of columns to retrieve (semicolon separated), filter expression, if needed and sort order (use //DESC suffix to reverse the order).

Behind the scenes

Behind the scenes our script uses Describe and Select methods from TeamDesk REST API. While Select() method limits output to 500 records, we are calling it in a loop to get complete data set. And we use Describe method to infer correct data types for columns to let Power Query to perform filtering and grouping specific to dates, times and durations.

The code is on GitHub.

Have fun!