From Azure DevOps to Power BI - Retrieve Description Fields, Acceptance Criteria and more

hi there friends welcome back to another video on paying it forward yesterday marked my 10year anniversary with Microsoft which was pretty fun and also 10,000 views on the channel I couldn't really believe it to be honest with you having started this in August but um I'm hoping this video in particular will give many more views um given it's built off of my um most popular video and a question that came up recently and something which became a bit of a head scratcher um and took me a bit longer to figure out than I'd uh initially hoped for but the question was is like when you are um you know returning Azure devops information inside of powerbi Sometimes some of the fields don't actually show up like the description field or the latest comment or the acceptance criteria and one of the things that all these fields have in common uh that you'll see when we get into it is that they're all actually rich text fields which mean they render differently to some of the other fields which may otherwise be like plain text so the challenge that I took on was how can you dynamically return that alongside the existing data that you're pulling from your existing let's say o data query that you'd use to return information from AZ devops and how can you then enrich it uh with information like the description the acceptance criteria and other Rich Text fields that you may want to use so fortunately after a couple of weeks of kind of working through this one uh we got there um and I think what's better to do than to jump straight into it it let's do it all right so we're all familiar I would say with the powerbi um but let's start by just getting the existing data from my um AZ devops project um and we're going to use the odata query like we did in the previous video for this so we're going to hit the O data feed and then I have a one note here a notepad with the existing um structure which I got from my previous video and then I'm just going to go over to my ad devops um project organization you can see I've got the org and the project here um and I'm just going to take those and basically bring them over into the structure of the um of the O data query so we're just going to come over here make the updates in here okay tidy it up so you can see the structure here is that we have just to kind of make it clear that we have the structure of the call that we're going to do so we're going to open this up and I'm just going to make it a little bit wider so we can see the preview a bit more easily um and as you go into the work items you'll probably notice already you can't see any of the rich text Fields so what you can see here for example is the user story um the title I mean and the work item type and some other uh key details but if I actually come in here to this user Story one which is ID number three you can see I've got like a description some exceptions criteria and I can't access that so the question is is like how can I dynamically access that so we're going to uh work on that today which is exciting um so I'm going to select the work items because I want to load those first as I've mentioned in the past it's always a good idea to transform the data let's bring this back over here all right so uh I've just left the work item id the title the work item type and the state um is what we want to have for now and so now we have this I was asking the question is like how do I get the description for the file so what I'm going to do is I went and searched for it and I found online this um great little example here on uh the powerbi um Forum um and it basically had this um query here um that I saw so let's give this one a go so you'll see if I bring this over into PBI um this one here and I go here new source blank query and then I go to the advanced edit and I basically put it in like this you'll see it gives you kind of some um options to populate here so we're going to do that again we're going to take the organization and the project and you can see this is work item number three okay and then we're going to change the ID to number three okay and as you can see very quickly um it generates it here and you can see I now have the description um and I also have the acceptance criteria so the question is is like how do you get this to look to look right so there's a couple of transform steps that you need to um work on so first thing we're going to do is transpose it so we're going to turn it from you know this way around into this way around to know the best way to put it we're going to transpose it and we also want to then take the first rows as headers and then you'll see we're kind of close to what we need um but if I now go across to the column where I get the uh you know the description and I click on it you can see down here at the bottom if uh if I put the text just make it a bit bigger I don't think I can do that but we'll just zoom in a little bit so you can see it you get a lot of this HTML um stuff going on within it um and if you're rendering that like if we just let's say um you know save this okay and we build a visual we've got the title work item id the type and the state and then over here we're going to bring the description you'll see it doesn't look very nice to say the least so um how do we first of all how do we go and make this description look correct so um in turn in other words turn it from you know um HTML into plain text so there's an approach that you can take to do that so what we're going to do is come back to the query um we're going to add a column a custom column like this and then we're going to use this formula inside of here so I'm just going to put description plain text all right and then in here I'm going to go and I've got this already saved from earlier is there's a little um you know structure here that you can do which is html. table and we can actually write it out to together and then you'll see that you then have the option to pick where it is so we've basically want the system if I can spell it right do description field and sometimes it will do this weird rendering thing so make sure that you you know pick it out correctly then you want to have a comma and then you open the what I call the squiggly brackets twice um and then we're going to put in text with a um Comm and then no spaces is that a comma yeah it is and then we're going to type in this one here colon and then root like that and then we're going to tidy it up tidy it up and then close the uh brackets and you can see we don't have any syntax syntax issues so we're going to add a column over here and what you'll see is maybe you can see this here you get this um new let's say column but it has this weird squiggly arrow thing at the top there um and what we're going to do is hit that and then what you'll see is it expands that table um so it based on the information that's available within it and you can see there's then a text option here and as you can see now we've got the um plain text back for that so if we quickly close and apply it all right so we have the plain text description and we swapped that out you'll see we now have a much cleaner experience for getting the description so um you know this is this is pretty cool right not too bad um you can then do the same for the acceptance criteria which for those of you with a sharp eye you would have seen was obviously in the original uh query as well which is over here um exceptions criteria um you need to make sure that you you know when you're um building that custom uh column that you refer to the column correctly otherwise it won't work or it will break but as those of you who probably know already this is good if you're only referring to one work item the question for me and this is where I would say the challenge came was how do I do this dynamically uh and when we say dynamically I mean like how do we iteratively do this um so that we can apply it to every work item that we have in that list so when we have this list of work items that we've got here how can we rather than do it in a separate table how can we add it as a column within this uh table um and this was where my area of learning really um grew I will say like that um is into using um you know functions inside of uh power power query to be able to do that work for you uh systematically so let's do the first one um and again I'm not going to write all of these out I will probably put them in I've I've already made a link to my GitHub repo which you'll find in description down below um and in there you'll find kind of the instructions on how to go and do this um which I've prepared for you um but for now we're going to start it like this so let's for now create a new source and we're going to do a blank query um and we're going to open the advanced editor I'm just going to make it a little bit bigger and then through working with chat GPD and other let's say other um approaches I came up with the following approach so let me talk you through it real quick so what this basically does is it says we're going to define a function with the work item as a number um anytime you have what I learned through my research is that anytime that you have this it's kind of creates a function um and you'll see each step is kind of explained um that's something chat GPT does a great job of doing so the first thing that it does is it fetch fetches the document using a dynamic URL um and it it's referring to the work item id uh which happens to be of course a column within the work items um uh table um then we uh record these fields um and convert them to a table um and what we're doing is we do this dynamically and then all these are all those transformation steps that we were doing before um including uh what it doesn't do at this point is it doesn't do the final step which is to you know change it from HTML back into um plain text but we'll do that together so we can kind of see it happen so this is the query I'm just going to hit done because um we'll see it run and what happens is you basically get back here um a parameter so it's asking for a parameter and you're like okay what does this mean so if I put in an example and I invoke the query you'll see I get back the actual work item um you know description for the work item number three but we don't want that's not really helping us yet so how do we actually include the details of this query back into the actual table and this is where it's kind of Genius so if you add a column and then invoke a custom function you can then pick the function and then it asks you what the what's the criteria that you're specifying that's used to populate that like in a in a kind of loop um and I'm just going to change the column name to um let's call it um Rich Text work item description okay and you'll see it's just running through for each of those all right um and it's returned back you know one of them that has a description just because right now you know I've only got one of the work items in this uh in this project with a description in it but we can quickly just do another one so we can kind of see it as it as it happens you'll see that we now have an additional um description there that we have for this one so just to tidy things up I'm going to quickly rename this um to uh description for now um I wanted to go one step further because this is great this answers the question that I got but I thought it would be kind of cool to do some other stuff as well so I'm quickly going to add the as well for the um uh acceptance criteria I've shared that with everyone in the um links in the in the description of the video um as well the structure for this one so you can obviously dynamically populate it with the information that you need um and again you'll we'll just quickly rename this to acceptance criteria and we'll also add this as a custom column as well again we can see it's giving a nice name to the column so we've got the exceptions criteria too um but there was a question and this was something actually that we've done kind of in our team in the past is is there a possibility not just to get the description but to also get like the latest comment um you know that's coming from the user story because what if we can see or we could even filter you know it's I think you can obviously extend this is in any way you'd wish to to make it work for your requirements but the question was is like can I also get this latest status so I can even report the status of how stuff is going within the um uh engagement or the work item that's being used um so I I decided to pursue that as well and see if I could get that to work the query is slightly different um that we're using so the function I should say so again we're going to start with the blank query go into advanced editor again I have it in the uh files so you can take a look at that it's slightly shorter because we're not doing all of the transposing that we did before um and you can then see here that I've got the um structure of the get the comments the API call is slightly different so you can kind of see it here that there's um it's uh yeah it looks a little bit different and it says to get the comments um and then you can see order by the created date and take the top one um so you could do things like filtering it based on whether it starts with um you know status or something like that if you did want didn't want to pick up all of the um you know comments that are being used but we I found this kind of fun um again you can see it adds it then as another function which we're going to call here the um uh latest comment um and we're going to add this as well um and what we've now got is uh we we now want to obviously get all of this tidied up so let's quickly just go through and do that I'll speed up the video at this part so you don't have to watch it all but um I'll make it so you can see I'm just going through and basically putting them all uh with that HTML to transform all of these into uh plain text all right and the last the last thing is obviously I don't want to keep the columns that I had um that were used to get the information um so in the transformation steps I can also get rid of these as well so which're is tidying it up um so now we have of course the three uh new columns that we needed in the table let's quickly apply these here so we can then look at them directly in the table all right so I'm just going to update this table get rid of that one um and we're now going to just take everything from we don't need to use that query anymore we can take the description text the acceptance criteria the latest comment um and you can see we can now bring all of these across and just tidy it up a little bit um just for folks in casee they're wondering you know if you wanted to this the way that it converts the HTML in this example is pretty let's say it it removes all of the formatting um so there are other approaches I'll provide some links um you know in the description of how you can actually make the um you know it keep some of the details for example like bullet points and stuff like that like if you see here in this acceptance criteria for this user story if I actually go to the example you can see it has bullets in numbers but again because they're like a HTML element it removes those um so it's something worth noting in terms of what you'd be doing but um now we've achieved this so it's pretty cool let's go and just kind of make a new comment because we said we wanted just the latest comment so we're going to update some of these other user stories here so we've got number two and number three so if I go back and then we can see here where this one is got the um uh we can even make it kind of bold and put some color on it just to kind of make it a bit over the top um and then we can go to the other one um here which is number six ID and we can say like subscribe all right subscribe and leave a comment so yeah don't forget to do that whilst you're here okay and now if we go back to the uh powerbi dashboard that we've just developed and we now hit refresh you'll see that we now have uh that information there and it's even kept the Emoji uh so pretty cool to see that all right friends so let's just do a little summary of uh what we've covered in the video today um so the core concept of this video was how can we actually get the description um and other Rich Text fields from within Azure devops and populate them inside of powerbi um and what we did is the first step we did was we basically um used the OD dat query uh that we have used exist in an existing video uh to basically return the work items um and their title and description um and then we initially did used an online uh approach which was to use a query which was kind of where you could say it's hardcoding the actual retrieval of the description field and other fields um and then how you actually you know present that data back and use it um and actually using that example I then was able to build um some functions that we can then use uh to be able to you know do all of those transformation steps dynamically on that list um so that we can then Loop through all of those work items we then put these into the same table as the work items table from OD dat so we're kind of merging both the OD data and the API queried uh information um and presenting it all back um as I mentioned to you I've presented this I'm sharing this on my um uh GitHub which is linked in the description um and you can find the full page here with how to get the work item description function um and I've made sure just to make sure you know make sure you update it with your organization and project and also that the you know when you're referring to the work it ID that it's um you select the correct column to obviously correspond with that um Dynamic value that you want to pass from your data um and also treating things as numbers is important if it is a number otherwise it won't render correctly put all of those there I will add the HTML one that I realized that I haven't put in there as well and yeah I just hope you find this really useful thanks again for the comment that I got on the original video uh that inspired me to uh make this video I hope you found it really helpful um for me it was a great journey uh into learning something new and I can see already the fact that I'll get to use this over and over again um and um I just finished a really big project at Microsoft so I now have a bit more time uh hopefully to focus on sharing some of the learnings that I did within that project and creating a couple more videos here on YouTube um so that's your sign to make sure that if you're not already that you subscribe um and leave a comment down below to let me know if there's any other ideas and uh things you'd like me to work on within this area and last but not least if you don't forget already don't forget to pay it forward take care have a good day

Channel: Paying it Forward

Views: 254

Keywords: #PowerBI, #AzureDevOps, #DataVisualization, #RichTextFields, #Microsoft, #PowerQuery, #DataAnalysis, #TechTutorials, #DynamicQueries, #OData, #HTMLtoPlainText, #GitHub

Id: snTNoOSGNsQ

Length: 21min 8sec (1268 seconds)

Published: Fri May 17 2024