Azure Devops to Power BI : Using API calls to surface additional data

hi there friends welcome back to another video on paying it forward this week I was quite excited because uh I got to do my first video based upon comments that were left on one of the videos uh which was on my most popular video yet related to AZ devops information if you want to take a look at that video the link of course is provided down below what struck me in relation to this question was that uh through the process of providing an ability to see data into azid devops in power this opened up the question what other data would I be able to get access to um and what was uh immediately difficult for me in terms of answering this question was how can you actually surface data uh related it to AZ devops in a way that then can be tabulated and presented back in powerbi so I found an approach after a lot of research that I thought was pretty interesting let's take a look at it for those of you who are regular viewers to the channel you may remember that this video back from August uh which has been one of my most popular videos which was all about crafting dashboards in PBI based upon leveraging the O data connection uh to data in um Azure devops and I recently got this question uh from one of the audience um which was particularly interesting um which was all around you know it seems when you go and try and use that OD connection that some of the information is not there for example the Pipelines table only seems to show the build uh pipelines um and the branches and repository information is missing so I've done a ton of research on this one because uh you know I do have a full-time job in the day as well uh but I love getting questions and I love finding let's say creative ways to try and find the solution to a problem that may be a common problem out there um and this may not be ideal for everyone but I wanted to show the approach or the workflow that I've taken um so that you could think about uh following it yourself um so let's jump straight in so firstly if I just show you here this is the OD dat connection that we get when we look into um ad devops data and you can see there's a bunch of different tables that are returned um that we can look into when you look into some of these though you'll notice that their repos is missing the information about the branches is missing and as was mentioned um some of the uh let's say release pipeline information is not there as well it just shows the pipelines and sometimes the pipeline runs so the question that I was thinking to myself was okay we have let's say basic work item information from the azra devops project but what if we want to go further and being able to um try this in a Smart Way um so I did a where I started first was I went on to chat GPT as I think anyone would do um and started using the advanced editor um and building um API C calls directly from powerbi desktop uh to um various different Services uh for those of you who are familiar there is a bunch of uh apis that are provided on um ad devops I've opened up for example the reference uh here for you um and these apis I've used a little bit in the past but my idea was like how can I use them to form information that I could then tabulate and then present back uh in the same PBI report where I'm using that OD dat query um to be able to present the work items um so I thought about it a little bit and because I'm a locco developer I took the following approach which was to leverage power automate and uh data verse um and the reason I did that is because I'm more familiar uh with that tool set however the concepts are the same here in that you could apply them yourself as a as a more skilled developer than me uh in that you you use the API call to basically uncover the information that you need you send that to a table and you use the information from the table to present it back into your powerbi dashboard um I think what's particularly interesting here is to see the approach I've taken in power automate uh so let's do that all right so first of all I have this ad devops project that I've created here on a demo tenant and you can see I have a couple of work items in the boards um and then I have three repositories that I've created here um what I'm going to do inside um this other repository here is I'm also going to create an additional Branch um which we're going to call um recording live um and then in the other one we're going to take another Branch um and we're going to call that we're going to create another branch and call that this is fun all right so now I've created the repositories and the branches for this uh little demo here uh we're now going to look at how I'm going to use power automate to connect all of this together so I've already created and tested this flow and I will walk you through what I did in each step first I'll give a quick caveat is that all of the connectors that I'm using are premium connectors I'm afraid that's me as a low Cod developer that's what I need because I um save a lot of time in not having to write the API calls myself for all of the different um outcomes here um and power automate does a great job of kind of making this really easy for anyone to do um so the first step I do is I just list the git repositories for the specific organization um and the project um and then what I do is I buil a table inside of data first that I use to capture all of the repositories and what this contains is it contains the repository name the repository ID and the URL if we take a quick look at the successful flow uh which I ran before on the very various repositories that I have you can see that there are four elements that are returned which is the ID the name of the repository the URL and the remote URL for each of those um and you can see here there's one two and then three that are returned using that repository information I then add these repositories to the repost table inside of data first that I created so I just pick the table from my data ver um instance I paste the name of the repository you can see I select it dynamically here and it says from the previous Step list of G repositories and I basically picked the name um and then in the advanced options you can then see ID do the same for the ID so this is for the repository ID that I just select like this and then also for the URL that I just select like this one the browser URL that completes the first step which is obviously taking the uh repositories and then storing them uh into to a table the next part is I want to get the branches uh for each of those repositories and also to then um uh work with those in this case there isn't a connector inside of power automate that will do this for you so what I've done is leverage to the API uh reference guide uh to basically return all of the different branches using the repository ID um as the unique um value that I then cycle through so you can see this is saying apply to each so it's basically looping through each one of those um repositories that it's found if we take a quick look at the outcome of that step uh you can see here's the API call the inputs that are going in here and then here's the body so if we just look at the raw outputs um and we scroll down a little bit uh from this the headers information at the top you'll be able to see that we have here the structure of the name of the of the branch um the object display name URL um ID unique name and other information and this this completes one um let's say branch that you would see um the one information that's important to note is that every one of the um branches is let's say has has something in in front of it which says refs and then heads in front of it so I take this sample output and what I do in the flow is I use that to create um a schema that I can use to pass the Json so all I need to do is take the output from here copy it generate from tample paste it and then hit done and what that will do is it will create a schema for me uh like this which you can basically see here and then what I'm doing is I'm taking the body from the previous step so I'm taking the branches information um using the body here and then that will basically um separate out all of the individual Parts you can see now what this looks like here so you can see in the Raw outputs that each of these are individual elements why this is important for me is that I want to be able to select each of them individually uh which is important for the next step so if I go now to the next step which is I want to take all of the branches that I've discovered for those repositories um and I want to Loop through each of those and add them to data us however as you noticed before I had the addition of the refs and then heads part to the branch name and I don't want show that up because that doesn't represent the branch name that was selected inside of azure devops um so I want it to show main or the name of the branch uh and remove some of that information the way that we can do that is I have an additional compost step here uh where I'm basically splitting the items that I received through the past Json step um at which which responds to name um so you can basically see if I just show you quickly here that past Jason step has taken each one of those parts parts of the schema and it's separated them out into each individual component and I'm taking the name element to then build the expression that I have in this step um and then what I'm doing after that is I'm basically splitting it where it says refs and heads um so that's the first part and then I'm saying what that would be the zero and then what comes after that is what I want to return so I'm basically splitting the value and then taking the second part of the value which would be what comes after uh this um let's say forward slash in the example here you can see we've returned main as the branch name and that's been built off here the ref's heads main if we scroll down a little bit more we can see there's another one um and it's detected that and now it can see that there's a second one and it's also extracted that as well last but not least we then take the branch uh table and we then add the outputs of this uh let's say cleaning up step where we're cleaning up the name of the branch and then what we do is because we have already the parent repository that that Branch belongs to we associate that as well um in a table and I'll just show you quickly in um data verse how I set that up so in data verse when I was building the structure to capture the data in a table I use what's called a lookup column in this parent repository lookup column you can see that I'm using it to look up the name of the um based on the repositories uh related table um and this will then create a relationship between these two tables so that if I were to for example go into the data here so if I just go into branches and I try to select a repository here it doesn't have any information in there at the moment but if I had a repository already captured inside of the database it would return the response here um and the way in which you need to relate those is using the odor ID uh which is the unique entity or the unique value uh that represents that line of where the data has been stored so it's very important to use this particular one that's coming from the previous table um here where it's representing the repository so that's kind of like an outcome of that step you can see what it looks like here so if I go into the um box and I go to the add repost to table you'll see it's actually the first thing that's respon returned which is the OD data ID which represents the row that the repository is stored in the repositories table so that I can relate the two uh together last but not least we just captur the URL uh should we give this a quick run let's do it so we're going to run this in real time going to hit save and test it validates that it has the right Connections in place we run the flow can see that uh it's very quick in terms of actually listing the information and it's taken place in about four seconds so we just have a quick look through we can actually see that it's returned three repositories in the first repository it's found two branches in the second one it's found two branches and then in the third one it's found two branches we can quickly check if that's right so yeah there's two branches in this one there's two branches in this one and then surprise surprise there's two branches in this one what we can also check and have a look at is to see an example of one of the uh Branch names so this is fun so we've definitely uh we're on to uh the right thing here so this all looks pretty good let's have a quick look inside of uh data verse to see if this information is also represented in our tables so if I come in first the repositories table you can see we have the three repositories that have been captured here here uh which are the um the ones for Peter 1 test one and test two which are all good and then if we go back to the branches we can also see that all of the necessary information has been captured here um and it shows the relationship between the branch name where we have three Mains but they're all related to the parent repository which you can see represented here we also have the URLs for those as as well so this is all great I've now got taken the step of getting the information of my repositories and branches and I now need to put it of course in powerbi if you've used powerbi a lot you'll notice that at the top here we have a data verse connector uh which is specific for dataverse and in this particular case it's really cool and very very powerful so once I open up the dataverse um connector I'll be able to see all of the list of tables and we'll have we'll quickly put in the the ones that we need uh which you can see here are represented by repositories and also by branches this will return each of the tables as queries on the left hand side that you can see here um and what I'm going to do just to make things nice and easy is tidy these up a little bit by removing the columns that I don't need um again you probably saw in my previous video uh this is a best practice that I think you should always try and do um so having a chance to do that will also be will always keep you kind of on top of uh your data and making sure that everything's nice and clean all right so my data is nice and clean let's now use it to build some additional visuals the first thing before I create any visuals is I'm going to look at the model view um and ensure that the relationship is in place um and what you can see here is that there's already a relationship between the repositories code and the link from the branches back to that so this is good because this shows that we have a relationship between the two tables already what this will allow us to do if we are in this particular case I've created some quick visuals here um so all I've done is I've built a slicer based on the repository name um and then I have a branch name here related to the branches that you would get from that related um repository if I do a quick refresh of the data because this was from an earlier um connection that i' done you'll see that there's been a little update in that each repository it this basically has a small table here that represents the repository information small table for the branches related to the repository and just a quick number telling you how many branches and this way you can just cycle through and see all of the different uh information that you need to get in obviously in this particular case I haven't done a API call for the pipelines uh the reasons for that is that I've never worked myself with pipelines uh inside of AZ devops um but but what I was able to discover uh just on that particular question is that there's a whole bunch of API information pertaining to the release details um and for me if you're going to end up you know wanting to build out um data for these kind of information you can actually just go and use um already um the apis um and kind of use it to build your own tables on the side that you can then use to present your ad devops data so let's quickly summarize what we've learned today first ly we got this great question which was all about how can you surface additional information inside parbi when you're leveraging AZ devops information when that o data query is insufficient one of the ways in which I chose to uh let's say tackle that problem was to use a combination of pre-built actions inside of power automate and also using custom HTTP calls uh which Leverage The API reference guide for Azure devops using a combination of these and then taking the outcomes of those I then wrote those to custom tables that I developed inside of data verse so that I could then access that data in powerbi so that I can then present that data alongside the work item information that I was already able to return based on what I'd done in the OD query originally so thank you so much for the question I always welcome many any of them that you have and love to kind of inter act and see if I can help solve some of the problems you're having dayto day um looking forward to seeing how you get on with this one if you have any questions don't hesitate to let me know thanks so much for watching the video today don't forget to pay it forward don't forget to comment down below subscribe and like the video see you next week

Channel: Paying it Forward

Views: 1,212

Keywords: Powerbi, Azure devops, Power automate, Paying it forward

Id: CINtyG6KsHw

Length: 18min 48sec (1128 seconds)

Published: Mon Nov 06 2023