{"id":1392,"date":"2018-01-15T06:27:30","date_gmt":"2018-01-15T12:27:30","guid":{"rendered":"https:\/\/www.teamdesk.net\/blog\/?p=1392"},"modified":"2018-01-15T06:32:55","modified_gmt":"2018-01-15T12:32:55","slug":"power-query-connects-excel-teamdesk","status":"publish","type":"post","link":"https:\/\/www.teamdesk.net\/blog\/tips-tricks\/power-query-connects-excel-teamdesk\/","title":{"rendered":"Power Query connects Excel to TeamDesk"},"content":{"rendered":"<p>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 <a href=\"https:\/\/teamdesk.crmdesk.com\/answer.aspx?aid=25065\">connector<\/a> for TeamDesk using Power Query <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/mt270235.aspx\">language<\/a>. <!--more--><\/p>\n<p>There are <a href=\"https:\/\/twitter.com\/mattmasson\/status\/862362092787171335\">rumors<\/a>, Microsoft wants to extend this tech to variety\u00a0of 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 <a href=\"https:\/\/www.teamdesk.net\">TeamDesk<\/a> in Excel right now.<\/p>\n<p><strong>Prerequisites:<\/strong> Excel <em>for Windows<\/em>. v2016 comes with Power Query Add-in preinstalled. For Excel 2010 and Excel 2013 you can <a href=\"https:\/\/www.microsoft.com\/en-us\/download\/details.aspx?id=39379\">download add-in<\/a> from Microsoft&#8217;s site. Unfortunately at the moment Power Query is neither supported on Office for Mac nor on Office365.<\/p>\n<h3>Add Power Query script to workbook<\/h3>\n<p><a href=\"#connect\">TLDR; I want to try it right now!<\/a><\/p>\n<p>Add new Power Query using Data | Get Data | From Other Sources | Blank Query<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1393\" src=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xlsetup-newquery.png\" alt=\"Create new query\" width=\"1114\" height=\"1100\" srcset=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xlsetup-newquery.png 1114w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xlsetup-newquery-300x296.png 300w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xlsetup-newquery-768x758.png 768w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xlsetup-newquery-1024x1011.png 1024w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xlsetup-newquery-60x60.png 60w\" sizes=\"auto, (max-width: 1114px) 100vw, 1114px\" \/><\/p>\n<p>In a query editor open &#8220;Advanced Editor&#8221;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1394\" src=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xlsetup-adveditor.png\" alt=\"Open Advanced Editor\" width=\"660\" height=\"302\" srcset=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xlsetup-adveditor.png 660w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xlsetup-adveditor-300x137.png 300w\" sizes=\"auto, (max-width: 660px) 100vw, 660px\" \/><\/p>\n<p>Now, open <a href=\"https:\/\/raw.githubusercontent.com\/ForeSoftCorp\/TeamDesk-Excel-PowerQuery\/master\/TeamDesk.Excel.pq\" target=\"_blank\" rel=\"noopener\">this page<\/a>, select all code, copy it and paste into advanced editor window.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-1395 size-full\" src=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xslsetup-adveditor2.png\" alt=\"Power Query advanced editor\" width=\"1395\" height=\"789\" srcset=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xslsetup-adveditor2.png 1395w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xslsetup-adveditor2-300x170.png 300w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xslsetup-adveditor2-768x434.png 768w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xslsetup-adveditor2-1024x579.png 1024w\" sizes=\"auto, (max-width: 1395px) 100vw, 1395px\" \/><\/p>\n<h2><span style=\"display: inline !important; float: none; background-color: transparent; color: #333333; cursor: text; font-family: Georgia,'Times New Roman','Bitstream Charter',Times,serif; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;\">Click Done. Now we should have new query listed. Rename it to, say &#8220;TeamDesk-Database&#8221; &#8211; name does not matter.<\/span><\/h2>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1396\" src=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xlsetup-database.png\" alt=\"First query, renamed\" width=\"1538\" height=\"789\" srcset=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xlsetup-database.png 1538w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xlsetup-database-300x154.png 300w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xlsetup-database-768x394.png 768w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xlsetup-database-1024x525.png 1024w\" sizes=\"auto, (max-width: 1538px) 100vw, 1538px\" \/><\/p>\n<p>Now, duplicate the query, rename the copy to, say &#8220;TeamDesk-SelectView&#8221;&#8230;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1397\" src=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xssetup-duplicate.png\" alt=\"Duplication query\" width=\"1538\" height=\"847\" srcset=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xssetup-duplicate.png 1538w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xssetup-duplicate-300x165.png 300w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xssetup-duplicate-768x423.png 768w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xssetup-duplicate-1024x564.png 1024w\" sizes=\"auto, (max-width: 1538px) 100vw, 1538px\" \/><\/p>\n<p><span style=\"display: inline !important; float: none; background-color: transparent; color: #333333; cursor: text; font-family: Georgia,'Times New Roman','Bitstream Charter',Times,serif; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;\">&#8230;and delete last step.<\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1399\" src=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xlsetup-deletelast-1.png\" alt=\"Delete last step\" width=\"1538\" height=\"847\" srcset=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xlsetup-deletelast-1.png 1538w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xlsetup-deletelast-1-300x165.png 300w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xlsetup-deletelast-1-768x423.png 768w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xlsetup-deletelast-1-1024x564.png 1024w\" sizes=\"auto, (max-width: 1538px) 100vw, 1538px\" \/><\/p>\n<p>Repeat once again &#8211; duplication TeamDesk-SelectView, rename the copy to, say TeamDesk-Select and delete last step. Close Query editor saving changes.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1400\" src=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xslsetup-select.png\" alt=\"Select\" width=\"1538\" height=\"847\" srcset=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xslsetup-select.png 1538w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xslsetup-select-300x165.png 300w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xslsetup-select-768x423.png 768w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2017\/12\/xslsetup-select-1024x564.png 1024w\" sizes=\"auto, (max-width: 1538px) 100vw, 1538px\" \/><\/p>\n<h3 id=\"connect\">One time setup<\/h3>\n<p>[<a href=\"https:\/\/github.com\/ForeSoftCorp\/TeamDesk-Excel-PowerQuery\/raw\/v1.0\/TeamDesk-Connected.xlsx\">Download script-enabled workbook<\/a>]<\/p>\n<p>Now you have Power Script-extended workbook. If there is a security warning about external data connections disabled, please enable.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1408\" src=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-firewall.png\" alt=\"Excel Firewall in action\" width=\"1535\" height=\"190\" srcset=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-firewall.png 1535w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-firewall-300x37.png 300w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-firewall-768x95.png 768w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-firewall-1024x127.png 1024w\" sizes=\"auto, (max-width: 1535px) 100vw, 1535px\" \/><\/p>\n<p>There are three function at your service.<\/p>\n<ul>\n<li><em>TeamDesk-Database<\/em> &#8211; prompts for URL of your database, let you select a table and a view from the list, gets the data from the view.<\/li>\n<li><em>TeamDesk-SelectView<\/em> &#8211; 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&#8217;s own filter.<\/li>\n<li><em>TeamDesk-Select<\/em> &#8211; this is a &#8220;raw query&#8221; version for even more flexibility. It prompts for URL, table, list of columns to retrieve, a filter and sort order.<\/li>\n<\/ul>\n<p>Let&#8217;s get some data using first function.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1405\" src=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-invoke.png\" alt=\"Invoke TeamDesk-Database function\" width=\"1869\" height=\"1223\" srcset=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-invoke.png 1869w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-invoke-300x196.png 300w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-invoke-768x503.png 768w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-invoke-1024x670.png 1024w\" sizes=\"auto, (max-width: 1869px) 100vw, 1869px\" \/><\/p>\n<p>Paste URL of your database<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1406\" src=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-invoke2.png\" alt=\"Paste URL of your database\" width=\"1538\" height=\"905\" srcset=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-invoke2.png 1538w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-invoke2-300x177.png 300w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-invoke2-768x452.png 768w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-invoke2-1024x603.png 1024w\" sizes=\"auto, (max-width: 1538px) 100vw, 1538px\" \/><\/p>\n<p>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 <em>Select which level&#8230; <\/em>box you can either select <em>&#8230;\/api\/v2\/<\/em> 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.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1407\" src=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-auth.png\" alt=\"\" width=\"1529\" height=\"900\" srcset=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-auth.png 1529w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-auth-300x177.png 300w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-auth-768x452.png 768w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-auth-1024x603.png 1024w\" sizes=\"auto, (max-width: 1529px) 100vw, 1529px\" \/><\/p>\n<p>And now, there is final step setup to complete. Let&#8217;s set privacy level to Public.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1409\" src=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-privacy.png\" alt=\"Set privacy level Public\" width=\"1533\" height=\"899\" srcset=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-privacy.png 1533w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-privacy-300x176.png 300w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-privacy-768x450.png 768w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-privacy-1024x601.png 1024w\" sizes=\"auto, (max-width: 1533px) 100vw, 1533px\" \/><\/p>\n<h3 id=\"connect\">Connecting to TeamDesk database<\/h3>\n<p>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.<\/p>\n<p>Click on a word &#8220;Table&#8221; to proceed to a list of views:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1412\" src=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-selecttable.png\" alt=\"Select table from the list\" width=\"1538\" height=\"905\" srcset=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-selecttable.png 1538w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-selecttable-300x177.png 300w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-selecttable-768x452.png 768w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-selecttable-1024x603.png 1024w\" sizes=\"auto, (max-width: 1538px) 100vw, 1538px\" \/><\/p>\n<p>Then again, on a word &#8220;Table&#8221; to get the data from the view.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1414\" src=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-selectview-1.png\" alt=\"Select View\" width=\"1538\" height=\"905\" srcset=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-selectview-1.png 1538w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-selectview-1-300x177.png 300w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-selectview-1-768x452.png 768w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-selectview-1-1024x603.png 1024w\" sizes=\"auto, (max-width: 1538px) 100vw, 1538px\" \/><\/p>\n<p>Now, Power Query will display preview of your data. Click close and load to insert the results to worksheet.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1415\" src=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-dataready.png\" alt=\"\" width=\"1538\" height=\"905\" srcset=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-dataready.png 1538w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-dataready-300x177.png 300w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-dataready-768x452.png 768w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-dataready-1024x603.png 1024w\" sizes=\"auto, (max-width: 1538px) 100vw, 1538px\" \/><\/p>\n<p>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.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1417\" src=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-refresh.png\" alt=\"Let's refresh\" width=\"1534\" height=\"896\" srcset=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-refresh.png 1534w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-refresh-300x175.png 300w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-refresh-768x449.png 768w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-refresh-1024x598.png 1024w\" sizes=\"auto, (max-width: 1534px) 100vw, 1534px\" \/><\/p>\n<p>And here I got new task back<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1416\" src=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-refreshed.png\" alt=\"First row in a new task\" width=\"1533\" height=\"901\" srcset=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-refreshed.png 1533w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-refreshed-300x176.png 300w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-refreshed-768x451.png 768w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-refreshed-1024x602.png 1024w\" sizes=\"auto, (max-width: 1533px) 100vw, 1533px\" \/><\/p>\n<p>In addition to manual refresh, you can set the worksheet to refresh the data after certain time interval.<\/p>\n<h3>SelectView function<\/h3>\n<p>Let&#8217;s have a quick tour for other two functions. Here are <em>SelectView<\/em> parameters:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1418\" src=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-selectview-params.png\" alt=\"\" width=\"1538\" height=\"940\" srcset=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-selectview-params.png 1538w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-selectview-params-300x183.png 300w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-selectview-params-768x469.png 768w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-selectview-params-1024x626.png 1024w\" sizes=\"auto, (max-width: 1538px) 100vw, 1538px\" \/><\/p>\n<p>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 <em>Project C.<\/em><\/p>\n<h3>Select function<\/h3>\n<p>Finally, <em>Select<\/em> 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).<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1421\" src=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-selectparams.png\" alt=\"Select function parameters\" width=\"1538\" height=\"1165\" srcset=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-selectparams.png 1538w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-selectparams-300x227.png 300w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-selectparams-768x582.png 768w, https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/xlcode-selectparams-1024x776.png 1024w\" sizes=\"auto, (max-width: 1538px) 100vw, 1538px\" \/><\/p>\n<h3>Behind the scenes<\/h3>\n<p>Behind the scenes our script uses Describe and Select methods from TeamDesk REST API. <span style=\"display: inline !important; float: none; background-color: transparent; color: #333333; cursor: text; font-family: Georgia,'Times New Roman','Bitstream Charter',Times,serif; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;\">While Select() method limits output to 500 records, we are calling it in a loop to get complete data set. <\/span>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.<\/p>\n<p>The code is on <a href=\"https:\/\/github.com\/ForeSoftCorp\/TeamDesk-Excel-PowerQuery\">GitHub<\/a>.<\/p>\n<p>Have fun!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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.<\/p>\n","protected":false},"author":4,"featured_media":1431,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-1392","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tips-tricks"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Power Query connects Excel to TeamDesk - TeamDesk Blog<\/title>\n<meta name=\"description\" content=\"Query your database from Excel using Power Query plugin and a little script from TeamDesk developers in a couple of easy steps.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.teamdesk.net\/blog\/tips-tricks\/power-query-connects-excel-teamdesk\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Power Query connects Excel to TeamDesk - TeamDesk Blog\" \/>\n<meta property=\"og:description\" content=\"Query your database from Excel using Power Query plugin and a little script from TeamDesk developers in a couple of easy steps.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.teamdesk.net\/blog\/tips-tricks\/power-query-connects-excel-teamdesk\/\" \/>\n<meta property=\"og:site_name\" content=\"TeamDesk Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-01-15T12:27:30+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-01-15T12:32:55+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/teamdesk-connect.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1000\" \/>\n\t<meta property=\"og:image:height\" content=\"600\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Kirill Bondar\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirill Bondar\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/tips-tricks\\\/power-query-connects-excel-teamdesk\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/tips-tricks\\\/power-query-connects-excel-teamdesk\\\/\"},\"author\":{\"name\":\"Kirill Bondar\",\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/#\\\/schema\\\/person\\\/22c4c05bd657513c8b00122fa364c8d2\"},\"headline\":\"Power Query connects Excel to TeamDesk\",\"datePublished\":\"2018-01-15T12:27:30+00:00\",\"dateModified\":\"2018-01-15T12:32:55+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/tips-tricks\\\/power-query-connects-excel-teamdesk\\\/\"},\"wordCount\":731,\"commentCount\":2,\"image\":{\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/tips-tricks\\\/power-query-connects-excel-teamdesk\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/wp-content\\\/uploads\\\/2018\\\/01\\\/teamdesk-connect.png\",\"articleSection\":[\"Tips &amp; Tricks\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/tips-tricks\\\/power-query-connects-excel-teamdesk\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/tips-tricks\\\/power-query-connects-excel-teamdesk\\\/\",\"url\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/tips-tricks\\\/power-query-connects-excel-teamdesk\\\/\",\"name\":\"Power Query connects Excel to TeamDesk - TeamDesk Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/tips-tricks\\\/power-query-connects-excel-teamdesk\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/tips-tricks\\\/power-query-connects-excel-teamdesk\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/wp-content\\\/uploads\\\/2018\\\/01\\\/teamdesk-connect.png\",\"datePublished\":\"2018-01-15T12:27:30+00:00\",\"dateModified\":\"2018-01-15T12:32:55+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/#\\\/schema\\\/person\\\/22c4c05bd657513c8b00122fa364c8d2\"},\"description\":\"Query your database from Excel using Power Query plugin and a little script from TeamDesk developers in a couple of easy steps.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/tips-tricks\\\/power-query-connects-excel-teamdesk\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/tips-tricks\\\/power-query-connects-excel-teamdesk\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/tips-tricks\\\/power-query-connects-excel-teamdesk\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/wp-content\\\/uploads\\\/2018\\\/01\\\/teamdesk-connect.png\",\"contentUrl\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/wp-content\\\/uploads\\\/2018\\\/01\\\/teamdesk-connect.png\",\"width\":1000,\"height\":600,\"caption\":\"Connect Excel to TeamDesk\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/tips-tricks\\\/power-query-connects-excel-teamdesk\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Power Query connects Excel to TeamDesk\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/\",\"name\":\"TeamDesk Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/#\\\/schema\\\/person\\\/22c4c05bd657513c8b00122fa364c8d2\",\"name\":\"Kirill Bondar\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/dc5bc844095b5753ccc73c589c028bf16615674f289668146bbd59205a08a52d?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/dc5bc844095b5753ccc73c589c028bf16615674f289668146bbd59205a08a52d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/dc5bc844095b5753ccc73c589c028bf16615674f289668146bbd59205a08a52d?s=96&d=mm&r=g\",\"caption\":\"Kirill Bondar\"},\"url\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/author\\\/kirill-bondar\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Power Query connects Excel to TeamDesk - TeamDesk Blog","description":"Query your database from Excel using Power Query plugin and a little script from TeamDesk developers in a couple of easy steps.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.teamdesk.net\/blog\/tips-tricks\/power-query-connects-excel-teamdesk\/","og_locale":"en_US","og_type":"article","og_title":"Power Query connects Excel to TeamDesk - TeamDesk Blog","og_description":"Query your database from Excel using Power Query plugin and a little script from TeamDesk developers in a couple of easy steps.","og_url":"https:\/\/www.teamdesk.net\/blog\/tips-tricks\/power-query-connects-excel-teamdesk\/","og_site_name":"TeamDesk Blog","article_published_time":"2018-01-15T12:27:30+00:00","article_modified_time":"2018-01-15T12:32:55+00:00","og_image":[{"width":1000,"height":600,"url":"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/teamdesk-connect.png","type":"image\/png"}],"author":"Kirill Bondar","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirill Bondar","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.teamdesk.net\/blog\/tips-tricks\/power-query-connects-excel-teamdesk\/#article","isPartOf":{"@id":"https:\/\/www.teamdesk.net\/blog\/tips-tricks\/power-query-connects-excel-teamdesk\/"},"author":{"name":"Kirill Bondar","@id":"https:\/\/www.teamdesk.net\/blog\/#\/schema\/person\/22c4c05bd657513c8b00122fa364c8d2"},"headline":"Power Query connects Excel to TeamDesk","datePublished":"2018-01-15T12:27:30+00:00","dateModified":"2018-01-15T12:32:55+00:00","mainEntityOfPage":{"@id":"https:\/\/www.teamdesk.net\/blog\/tips-tricks\/power-query-connects-excel-teamdesk\/"},"wordCount":731,"commentCount":2,"image":{"@id":"https:\/\/www.teamdesk.net\/blog\/tips-tricks\/power-query-connects-excel-teamdesk\/#primaryimage"},"thumbnailUrl":"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/teamdesk-connect.png","articleSection":["Tips &amp; Tricks"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.teamdesk.net\/blog\/tips-tricks\/power-query-connects-excel-teamdesk\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.teamdesk.net\/blog\/tips-tricks\/power-query-connects-excel-teamdesk\/","url":"https:\/\/www.teamdesk.net\/blog\/tips-tricks\/power-query-connects-excel-teamdesk\/","name":"Power Query connects Excel to TeamDesk - TeamDesk Blog","isPartOf":{"@id":"https:\/\/www.teamdesk.net\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.teamdesk.net\/blog\/tips-tricks\/power-query-connects-excel-teamdesk\/#primaryimage"},"image":{"@id":"https:\/\/www.teamdesk.net\/blog\/tips-tricks\/power-query-connects-excel-teamdesk\/#primaryimage"},"thumbnailUrl":"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/teamdesk-connect.png","datePublished":"2018-01-15T12:27:30+00:00","dateModified":"2018-01-15T12:32:55+00:00","author":{"@id":"https:\/\/www.teamdesk.net\/blog\/#\/schema\/person\/22c4c05bd657513c8b00122fa364c8d2"},"description":"Query your database from Excel using Power Query plugin and a little script from TeamDesk developers in a couple of easy steps.","breadcrumb":{"@id":"https:\/\/www.teamdesk.net\/blog\/tips-tricks\/power-query-connects-excel-teamdesk\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.teamdesk.net\/blog\/tips-tricks\/power-query-connects-excel-teamdesk\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.teamdesk.net\/blog\/tips-tricks\/power-query-connects-excel-teamdesk\/#primaryimage","url":"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/teamdesk-connect.png","contentUrl":"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2018\/01\/teamdesk-connect.png","width":1000,"height":600,"caption":"Connect Excel to TeamDesk"},{"@type":"BreadcrumbList","@id":"https:\/\/www.teamdesk.net\/blog\/tips-tricks\/power-query-connects-excel-teamdesk\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.teamdesk.net\/blog\/"},{"@type":"ListItem","position":2,"name":"Power Query connects Excel to TeamDesk"}]},{"@type":"WebSite","@id":"https:\/\/www.teamdesk.net\/blog\/#website","url":"https:\/\/www.teamdesk.net\/blog\/","name":"TeamDesk Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.teamdesk.net\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.teamdesk.net\/blog\/#\/schema\/person\/22c4c05bd657513c8b00122fa364c8d2","name":"Kirill Bondar","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/dc5bc844095b5753ccc73c589c028bf16615674f289668146bbd59205a08a52d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/dc5bc844095b5753ccc73c589c028bf16615674f289668146bbd59205a08a52d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/dc5bc844095b5753ccc73c589c028bf16615674f289668146bbd59205a08a52d?s=96&d=mm&r=g","caption":"Kirill Bondar"},"url":"https:\/\/www.teamdesk.net\/blog\/author\/kirill-bondar\/"}]}},"_links":{"self":[{"href":"https:\/\/www.teamdesk.net\/blog\/wp-json\/wp\/v2\/posts\/1392","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.teamdesk.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.teamdesk.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.teamdesk.net\/blog\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.teamdesk.net\/blog\/wp-json\/wp\/v2\/comments?post=1392"}],"version-history":[{"count":13,"href":"https:\/\/www.teamdesk.net\/blog\/wp-json\/wp\/v2\/posts\/1392\/revisions"}],"predecessor-version":[{"id":1434,"href":"https:\/\/www.teamdesk.net\/blog\/wp-json\/wp\/v2\/posts\/1392\/revisions\/1434"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.teamdesk.net\/blog\/wp-json\/wp\/v2\/media\/1431"}],"wp:attachment":[{"href":"https:\/\/www.teamdesk.net\/blog\/wp-json\/wp\/v2\/media?parent=1392"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.teamdesk.net\/blog\/wp-json\/wp\/v2\/categories?post=1392"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.teamdesk.net\/blog\/wp-json\/wp\/v2\/tags?post=1392"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}