3/30/2015

Power Query OData Calls to TFS

The past few weeks I've been working with Microsoft Excel.

My project consists of creating Power Query calls to an OData service from Team Foundation Server.  That entails writing customer M language queries to connect to the server, specify which table to pull data from, transform the data, expand columns and store either in Power Pivot or an Excel tab.

Since we are pulling from a hierarchy, first we grab the Features, then the User Story and Bugs and finally the Tasks.  Parent, Child, GrandChild relationships.

I first tried to bring in each of the tables to Power Pivot and apply the joins there.  However for one reason or another, the joins are not behaving properly.

Next, I tried to call a recursive function to go out and grab all the linked Ids along the hierarchy.  In doing so, it only keeps those records that have Child relationships so by the time the query finishes, the data set is stripped down and many records are missing, those records without matching records in all three tables.

Currently trying another approach, that is to pull each of the three tables separately.  Then apply a recursive function to Left Join the tables.

For the latter two attempts, I've leveraged code found on Chris Webb's BI blog, much appreciated.

https://cwebbbi.wordpress.com/2013/06/22/flattening-a-parentchild-relationship-in-data-explorer/

To write the code, you can use notepad++, which has a plug in for the M language, you can download it here:

http://www.mattmasson.com/2014/11/notepad-language-file-for-the-power-query-formula-language-m/

The goal is to have the Model built so that we can create Pivot Table reports for the client.  They are currently using the TFS Plug-In and assembling the data manually.  Our approach should streamline the process.

I was surprised how versatile Power Query was.  One of the hardest parts of Business Intelligence is the Extract, Transform and Load.  This tool allows people to bring in data from just about anywhere, massage, tweak and transform it and create a model for consumption in the Power BI Suite.  After the initial learning curve, I find it very flexible and easy to use.  It's similar yet different than traditional SSIS, and it's built into Excel, so business users have access to it.

Power Query is a cool product from the Microsoft Stack, built into Excel as a plug in.  I recommend it.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Have You Seen My Double