Integrate OpenAI Models (like ChatGPT) Into Your Power BI Dashboards Using Power Automate

hi in this video we're going to be looking at how you can pass data from your powerbi report to an open a model process the data and get a response back and then display that response in your powerbi report all this is Jason from effective dashboards helping maintenance and reliability professionals get the most out of powerbi so welcome back to another video so in this video we're going to be looking at running a flow here that's going to pass some data that you select and also a question to an open a model and then we're going to get the response back and that response is going to be also integrated into our powerbi report so it's going to augment it with some additional data now the use case I've got here is that we've got a work order in fact we've got a lot of work orders here and we want to be able to carry out some analysis on the quality of the data that's entered into these work orders okay so it's a QC analysis and um what we're going to do is we're going to pass out this data here which is a description of defect and and we're going to set up a prompt that's going to instruct the large language model to look at that data compare that against something that's good practice and come back with some feedback now first of all it's going to create a general assessment then it's going to give us some feedback on its assessment the data here then it's going to provide some coaching tips so that we could use that data to provide some coaching to the person that's entering that information and then it's going to give it an overall rating from 1 to three okay so let's quickly start by going through the setup we've got a list of work orders here and each one of the work orders has got a number it's got um a description it's got various other bits and pieces but the key thing we're going to look at in the the the data that we're going to analyze is this defect report in here so that's a a long text free text information about the the defect that's actually been reported and that is the the field that we're going to analyze using the capabilities of the large language model okay so in this example we're going to use open AI um we're going to send that text to open AI with a prompt that's going to ask it to analyze that and then it's going to provide us a response and that response is going to be its opinion on how good that text is how what's the quality of that text now typically you would actually if you think about it you'd want this done at the point that analysis to be done at the point when somebody had actually just entered the data so that you could provide the feedback instantly they could go back and make some changes make from some modifications and then that that feedback loop would be done there and done and dusted it would be quite quick however that might be might not be possible it might well be that it's too costly it's not technically possible might take too long and you want to start getting some feedback and some analysis done of this type of data um quicker so this is an example where you can use the um the use powerbi to actually create that information Okay so we've got that now once the large language model passes back the information we need a place to store that now you could write it back to the the database and I would probably that's the ideal situation is you create a field within the table and you write you back to that field but for now and for the EAS of this demo we're going to create a SharePoint list and that's going to be the place we're going to store the response from the the large language model so let's go and I'll just quickly show you that okay so it's a really straightforward SharePoint list here it's got an ID which is a an auto populated ID it's got a work order number and it's got the defect assessment and that's going to be the place where going to store the information so when the information comes back when the response comes back it's going to be put into that field here now one thing you do need to be aware of is that this field here needs to be a format that is multiple lines of text okay because it's going to be quite a big response okay so once you've done that I've just put in some some dummy data here for just now we can delete that later then we need to go and copy this part of the URL and we're going to import this into our powerbi so you copy this URL here and we're going to import this into the powerbi report okay so I'm back in my powerbi report going to go to get data more then I'm going to choose this SharePoint online list and then we're going to paste the URL in here now at this point in time you might get a popup asking to validate your credentials and log in with your your Microsoft account so you need to do that I've already done that um so I'm going to go and find this list here so here it is here work Ord our AI assessment results okay and there's the data here there's a we refresh button here if you need to refresh this um just to see what the latest preview is and we can see that it's got a lot a lot of data here but we're going to go into this transform data and then we're going to only there's lots of fields in here that gets added to the to the list automatically so we don't need most of these so we're going to go to choose columns and then the only fields that we actually need are going to be the work order number which is here and the defect report response assessment okay so it's been trunky there but that's fine okay so that's the only felds we're actually going to pull in here in fact actually I'm going to go back in I'm going to choose another field I'm going to choose the created date as well because it is useful to have a time stamp in here so I'm going to choose this created okay so we can see there's some information there's nothing in here for just now but that is us so we're just going to go and load that in okay so we can see these data types here are all unassigned so we're going to select all of this and I'm going to go to to transform and I'm going to go to detect data type and just make sure that it's all given the right data here so we've got this one here is still undetected and this one here is going to be a date okay cuz it's null so we've got to go and actually tell so for this one here we're going to go to text okay so then we can load this in okay so that's loaded in the next thing we need to do is establish a connection so here that is here it's all on its own now we need to make sure that this work order number here is connected to this work order number in here this work order ID so let's just establish that relationship there okay it's a one: one now ultimately there might be multiples in here so let's just change that and we'll do this as a one to many and I'm also going to change this cross fill to be single okay okay so that's our data model place this means that we can now have a connection a join between this table here the the workorder AI assessment results um through the work order ID to the workorder ID in the work ORD table so when I pull in any field here there's going to be a a relationship established here and we can we can display that so let's pull in the defect report assessment now there's not going to be anything in here just now because there's no there's not any work orders in here for it to link to this def defect assessment but once that starts starts to come through into the data model we're going to see that next to each of the work orders right so the next stage is to add in our power automate so what we're going to do is we're going to add in this power automate which is still in preview mode it's been there for ages but this is a a button this inserts a button that triggers a power automate flow now the first thing we do before we go in and start to create the flow is we establish which data which columns essentially that decide the data set that we're going to pass across to the par automate flow so the data that we want to pass across is going to be from the work order table and it's going to be the work order number okay and it's going to be the defect report okay so this column here so that's the text we want to analyze and that's the text we want to have available to us to pass across to the openi model so work order number and the defect report okay so that's there now and then we go into this mode options here and then edit and that then opens up the power automate flow Builder within powerbi so once you're here you can connect that button to an existing flow but we're going to go and create a new one so we're just going to go into new and create an instant Cloud flow and this is where we start to build the steps okay so the first step is going to be basically to call the API for large language model from open a now before we can do that the first thing we need to do is go and actually get an API key and also get some of the settings but I'll um I'll go in and just put the first action here which is the HTTP and this is the action we're going to um use here it's an HTTP call now it is a premium connector so you will need um a premium account to be set up so is an account you need to pay I think it's about 15 I think in the UK it's like5 a month to get this access to these premium connectors so you will need that to use this approach okay so in here we can see we've got the the different parameters so we need to figure out what parameters it is we're going to send across to the openi API so let's go in to the openi website and we'll get that details okay so if you go to open.com and you'll need to create an account so I've got one so I'm just going to log in and then we've got some options here we're going to go to API and we're going to go to first of all personal okay now in here you can view API keys so this is an area where you can go in and create a new key okay so you just click on that button you give it a name it'll generate a key for you you need to copy that key you won't get access to that key again okay so you need to copy it and paste it somewhere in the notepad somewhere for just now um but generate that that key and that's going to be what we're going to use as part of the API when we pass that across to the model the next thing we're going to do is go to this API reference okay so once we're here we're going to click on this making request so they're here and the first bit of information we need is this URL here so I'm just going to copy this and I'm going to go back in here and I'm going to first of all select this as being a post and then I'm going to paste that in there okay so now we've got headers um header keys to put in so these key and value pairs we need to actually go and get some information put into here so let's go back here and here's the here are the two different header pairs we need here content type and authoriz authorization B so let's go and copy this content type first of all we put that in there and we'll use that in here remember copy authorization and then in the second bit we need the word b and then a space and then in here you need to put your key okay so the key was the key that you created just um a few a few minutes ago in the video there okay so we'll just save that and that's our first bit done and then the next bit we need to do is put in a body okay so this is the body that we need and we just need the the basically Json format here that's in between these curly blackets so let's copy that and we're going to paste it into there we just make a little format and change here just to make it a bit easier to read so we've got a few parameters that are passing across so this is going to be the request that we pass across to this URL here the open a model so here's the model we've got we're going to be using here is um GPT 3.5 turbo and you can change the model to be whatever model it is you want but we'll leave it that one for just now then we've got a message and the message consists of two parts here we've got the rule and we've got the content okay so what we want for this is we've got a rule as basically rule set up as a user and this is a Content so as a user we're passing across um a prompt here that's going to say say this is a test okay that's all we're going to do and this is the equivalent of typing if you ever use chart GPT or bird anything this is equivalent of you typing into the the chat okay this message here okay so the next thing we going to do is add in a new Step here and then we need to have basically we're this is going to generate a response but we need a place to store that response so that's what we're going to do next so we're going to go in here to an option which is going to be called pars Json okay because it's going to provide a a file format that's going to be a Json file as a an as a response as an as an output for the model so this par Json is going to be where we capture the response okay so the content here we're going to use this Dynamic content is going to be the body okay so the body of the HTTP so it's going to be the response here or well that's going to be the whole file here and then we need to provide it with a schema now you can go in and generate from a a sample so we're going to press that button here and then we're going to go back to the open ey site and if we look here it says that this basically you should get a response back that resembles a following okay so this is the file that we're going to use as a an input into this section here and then we're just going to say done but yeah that's it okay yep so this is going to be then um the Json format the template or the schema that's going to basically be able to store that data so that is um done the the first part of this so let's see if that and then we'll save and apply and then we're going to go back in here okay so let just edit this and we'll change the name and we'll save that and then we're just going to apply that successfully applied that's good then go back to the report and we can see we've got a button here to run the flow so let's just run this flow I just want to make sure that everything's working and we're getting we're able to post the the um request and get the response back so we can go in here to edit okay so can see it was a success so let's go and click on here and then it kind of breaks it down so let's see what happens here everything looks okay this a test we just Ed that EXA stuff what I'm really interested in here is that we got something back this is a test okay so we got that back so before we do that actually that was a standard stuff so let's just go and change this so to change it we're going to go back in here we're going to go to edit I'm going to go in here and I'm just going to go and ask a different question okay what are the five stages of the work management process so let's save and apply that okay go back to report let's run it out we'll go back in and have a look at the results okay so it exceeded again and then we'll just go straight in here and if we go to the outputs here we go output of the body and here we can see the five stages of work management process of planning etc etc so we can clearly see that we're getting a response here great so that is now set up the model which is good so now we're going to ask it a better question so in here instead of just asking this question here we're actually going to put in a prompt here that's going to ask it to go and review assume the identity of a Work Management expert first of all and then go and review the data against best practice okay so your task is to create a review of the quality of the work order defect reporting text and one provide your expert analysis feedback on the quality of information against generally accepted Work Management best practice practices and then step two is suggest up to three actions to take to improve the text and then step number three is after reviewing the text provide a rating of the quality user scale 1 to three and then the scale from the 1 to three is 3 equals good and then basically I've just I've asked it to I've basically laid out what one two and three means okay so I'm also going to give it some additional information here related to to the the output format it's important that the response is structured in this format one General assessment are the contents of section one feedback here two coaching tips um so it's going to be the coaching tips that we're going to get from here so suggest up so this is section two suggest up to three actions to take to improve the text and then section three is going to be called rating just add a number from 1 three here nothing else this number should be the last col turn the response now I'm I'm telling I'm telling to respond with the last character because I want it to be easy for this to just to draw that out from within powerbi so we can isolate that number and then we can use that as a metric in some of our reports okay so that's uh there um I think I might need to finish it off with a comma right so let's save that now the next thing we need to do is pass it the text so if we go back up to here um your task is to create a review the quality of the work order reporting text provide the feedback on the quality information generally accepted Work Management best practice right so I'm going to put it in here so what we can do is we can click in here and if we scroll down into here we can actually look for the input from the button which is going to be this here uh which is going to be this here this defect report okay now as soon as I've added this you'll notice something it's automatically encapsulated this HTTP response within and applies to each okay that's because there could be multiple those in here okay we want to do it for each one of the rows so by adding it into here it's recognized that this is a data set that's got multiple rows in it potentially so that's why it's um added this applies to each in here so that's fine it's exactly what we want so let's save that okay now what we actually want to do is we want that to also be in here so I'm going to go in here and I'm going to copy this a clipboard because we want the action to be part of that applies to okay because if you remember what we're going to be doing we are going to be if go in here go to my clipboard and then just paste it in there okay and then we can delete it from here okay so for each item in the list of work orders that we're going to pass we want basically it to go in look at the defect text which is going to be this here that we've passed out and then get that response back here okay and that's going to this is going to be the response that we're going to use and then the next stage we're going next thing we're going to do with that response is we're going to write that as a record to the SharePoint list okay so in SharePoint I'm going to go and type in SharePoint here and it's going to be called create item and then we're going to go and hopefully get our SharePoint on here which is this one and then the list name recorder a assessment results and then we can see we've got the the fields that we added in here now the title um we are going to have to put something in here so let's let's just put in let's just type in title here and then in here what we're going to do is work order number remember was passed as part of the original data set so it should be down here here we go workorder number and then this def defa report assessment OPI is going to be what is been returned in the content okay and again it's it's it's put a applies to each around here but that's fine it's just because the content can be multiple rows but in our instance it's not but we'll just leave it as it is and we can see now we're going to right back to our SharePoint list with the title is going to be the text title um the work order number is going to be the work order number that we actually originally had from the the past from the from powerbi so we're going to write that back into the SharePoint that's going to allow us to establish that connection and then the content is going to be what was generated by the large language model by open Ai and pass to this Json file and we've just basically picked the content out of that Json file Okay so so let's just save that and we'll apply it and then we'll go back to the report now this is probably going to be a two-part video because it's already quite long just now I'm just going to basically show you working in in the setup okay in the next video we're going to go into some safeguards because if you've got a th000 work orders here and you press this one flow it's going to pass a th000 work orders and it's going to process it a thousand times we don't want that but just for the purpos of this video just so you can see the connection and get it established and understand how you enter how the information can flow back and forth between open AI what I'm going to do is I'm going to select one of these because I only want to pass one work order across okay if I select two work orders two work orders will be passed across as part of this data that's passed to this flow here so then I'm going to run it I'm going to go in and edit to see if this flow run okay and it says it exceeded which is good okay so let's see what happened here so we've got the response that was the input prompt we did and if we go down here that was what was passed let's go into this one here and the output okay so General assessment so we can see it's definitely created something and then let's see hopefully it did add something in here so we'll see in a second it looks like it passed something across and was successful so let's go back to our report and we're going to go and refresh it okay so here we have it we've got just make this button a little bit smaller we've got an assessment here okay so this is the text now this text could have been larger it could have been um this was just text that I automatically generated I actually got chat G GPT to generate this text but here's the assessment here so what is it saying it's basically saying well actually let's make it slightly smaller first make it a bit more vable the information provided in the work order defect report text is concise and covers essential details required to address the issue the defect location observations and actions are clearly stated so we can see that it's giv us some feedback here and then it's saying the coaching tips include additional details about the AC unit including the model in the previous maintenance history and provide the context of troubleshooting potential Solutions specify the troubleshooting steps are taken before to determine that the compressor needs to be replaced this can be this can avoid the necessary replacement so these coaching tips could then be fed back to Mark Smith to say okay here's some information that you can use to improve the defect report the next time and then we've got an over allting here okay so I can go ahead now and run it for the other ones so I'm just going to fill that number one and then I'm going to run the float okay so that's going to cycle through each one of them and it's going to go and car an assessment of this defect report okay so this triggered in the flow now let's see how it's getting on now this one will take a lot longer because it's going to have to go through 19 more orders rather than just one okay so we can see that's running we click on here we should be able to see it in action okay so here's the the 20 there so this will start to appear once the the calls have been made and the responses have been generated so we'll leave up for a second okay so that run successfully and we can see here that we have got all of the different assessments here against the the work orders now there's a couple that have came through as duplicates it's because I've probably pressed the button twice so we're going to go and but but but basically the information is all here it's all good stuff this is going to take this this potentially Save hours of basically reviewing the quality of the the the work orders that are entered into the system which would have been a manual process but also putting together the coaching tips that are going to be tailored training that's specifically for each of these individuals here so that's the second benefit of this is that you've actually got some tailored feedback that are that's based on the data now can you imagine how much time it would take for somebody to do that manually or basically how inefficient it would be if you were just to do blet training to everybody and then we've also got finally this ability here to turn that free text here into an actual number okay so we can actually get a rating here and then it's going to give us the capability to go and carry out some metrics it says okay how many work orders have we getting through the system how many are in each of the different categories okay how many are poor how many are okay and how many are excellent um and then that is it let's see if there's any that were most of them were the two at the moment here's one that's have eight and one okay so we can see that this one here has got this defect here was was assessed as being a one okay so that's it for the first video in the second video we're going to go and we're going to finesse this a little bit we're going to create some measures to look at the some of the metrics here we're going to go and actually put in um some safeguards that mean you're not going to get duplicates like I've got here so once you've submitted it we're going to make a check to make sure that you've um we're not going to submit something twice because it's going to cost us money and it's going to basically make us have two assessments we will really need one um we'll do a few other bits and pieces just to find you in the model okay so thanks for watching and I'll talk to you in the next video

Channel: Jason Davidson

Views: 1,711

Keywords:

Id: z7Z6EqtwqQQ

Length: 27min 42sec (1662 seconds)

Published: Sun Oct 22 2023