It's Some Guy Named Chris and in this
video we're going to utilize ChatGPT within Power BI. And return an analysis of data. Now
I know this is something that ChatGPT or Power BI already
does. The natural language, you can do AI through cognitive
services and even the analysis. But I wanted to see if ChatGPT can take a value from Power
BI and return it. So we're gonna show you how to do that right
now. What we're gonna do is Start off by opening Power BI. For this example I want really
simple. I'm gonna go real simple. We're gonna start blank. We're
actually just gonna enter some data. So let's call this "Category". Put in bike, Drain, and Computer. These have nothing Quantity Bikes. Potholes. Drains and computers. Not sure how any of these things
correlate, but. Seems like a fun thing. Make up like. So real quick we can load in a
table. And just for. Fun, we're gonna add this to Just a nice little grid. It's not going to mean much, but
it just gives us the visual solid returns of value. So from here what we're gonna do
and there's a lot of articles out there. So I
followed a couple that are integrating ChatGPT into Power
Platform. Power Automate. But what we're gonna do is come
out and we're gonna insert. And that's what we're gonna end
up using, Power Automate. To really get us the results. Now I'll show you how we still
need to return the data into something, so we'll do that in a
second. But what we can start by adding is. We add these three values. Again,
it could be anything in your data set. We're then going to come in
here. And create a new Power Automate. Now when you start looking at
the available Power Automates, nothing really matched. So what
we're gonna do? Instant cloud flow. So we're
gonna click a button. You can automate this a little more, but
right now there's no additional steps we're gonna go to. It's a premium connector. And we're going to have methods.
Now, where are these methods and gonna come from? This is where
those other videos come. A great one from Giuliano De Luca was
online, but we're going to step through as well. Hopefully ChatGPT is available. That's good. But we're also going to go. API, now we're gonna have to log in
here. Not a big deal. Once we are logged in. Documentation. And we're going to go to API.
Now to get our API key, We can go to personal View API
key and you can see I've already created an API key so I'm not
going to create that again. But you know, looking into the
side, because I have a copy for it for later, but what this
value does There we go. Save that. What in here important aspect is
if we go back to documentation. Making requests. This is where
we can get our information. So what we're going to want is. Completions. We're gonna do a post. Now we've got two headers
according to the documentation. Content type. And authorization. And this is where we are going
to enter the API key, which is the key that we have. And then in the body down here. You've got different options to
return or to get the body. What I did for mine, is I
came down, And I pulled it out of this
completion here. So it's. This body right here. Now there are limitations and
this is where the maximum tokens run into this by testing this on
other data sets. The request and the completion
can only be 4,096 so you have to make sure
that you're not sending in too much data because it needs to
return at least. Well, sending too much data has
been hit 4000, but also that's the combination of the return
and the request. No extra seven. I'm gonna go
1500. And we're going to say this is a
test, we're going to change this and say What is Power BI? At this point, we're gonna hit
save. Now. So far, this is what's
gonna post. It's gonna return, but how do we get that data into
Power BI? So. When I was first doing
this, I went to Power BI and I'm like, oh, we can return it into
about a field. We weirdly can't. I'm sure there may be other ways
to do this. Exporting OK, refresh your data set. But what I liked about this was. We can do. Add rows to a data set. Now. As you will learn this data sets
if I come out here. Demo. I got a ChatGPT dataset. I
have a lot of other data sets inside of Opal demo, but these
data sets. Are the real time data set. So
what we're going to do is go out to Power BI. And we're going to come down to
a streaming data set. API. This is going to be a ChatGPT What we're going to do is just
call it reply. That's gonna be the only value that we're gonna
have now. Do I recommend having a
timestamp? Yeah, something like that. That you know, but we're
gonna have reply. The other thing I was looked at was the
historical data. I did end up turning this on because it kept
wiping out some of the data. Again, this is a demo for
everyone to just play with. I think some of the things I'll
show you and recommendations that are online you can improve on this. But
for now, just to see how it works, we're going to reply. So. All this is that for now, we
have to push up here. We don't need it at this point. Get a new ChatGPT reply. So we're gonna go back into Our report. Always like to just save it. Create a new step Add rows to a data set. Demo. I should see two, Yep. And the table is real time data. On our HTTP reply we have a status
code. What we really want is inside the status code we've got
text. So what we're going to do Is do JSON parse This point we're gonna take the
body of the return, and then we're gonna get the scheme. Now
where's the scheme? What's nice is You can get it directly from. Documentation. We know with Power Automate we can
generate from the sample. Now as we go into the data set
here. We have different fields and
we're going to select. Text. At this point we can save. That would save and apply. And now you can see that we
applied the button to number 2. Up here What I'm going to do is call it. Reply. We'll go back to the report. Now This allows it to run flow. Where are we entering the data? Where are we seeing the data? So in
here what we're going to do Is come out here to the Power BI
datasets and before I click this button I want to show you where
I had to set this. During the testing I did have to
come into the options. And it actually told me to do
this and I had to enable direct query for PBI datasets. And this
is just FYI, you have to set that. Restart Power BI desktop. One second once you set. Doing that allowed me to connect
to. Different. Notice I keep spelling ChatGPT wrong, but. Connect. You want to get the real time
data. Of course I trust it. No, I've got a single field. I'm gonna call it reply. I'm gonna keep it as a table of
sort so we can see. At this point we can save. I always like that I can save Up here. Triggering the flow. Now I don't know about you. As I
trigger flows, I always go like Make sure my flows are working and
you will be able to see the flow within that power
automate but all of the features are not present, so just be
aware. 9 seconds looks like it
succeeded. Come back into here. And I refreshed the data. There we go. So we're getting
responses. This is perfect. So now this is where I think we can
come in. And make improvements. So. If I edit the flow. Open the chat, automate that I
want. This is where we can actually
include the data from that grid. So what if I asked? Please, I don't know why I
always say please. Okay Please. Actually. Keep it right there. And down here I can actually
put. Power BI data. The data that I sent
in is the whole data, not just the sum the item. You can do an item. But I'm doing this for the whole data
set. Now Initially, and this is the JSON
thing, initially I tried to do an and and add. It didn't really
work so I just included right into the string. Reply. Back to here again. There we go. The data looks normal. There's nothing odd that's out of the ordinary. So what if we went in Again, this is just a test. So I think one of the things I
would like to do. Is. It's been a while. Determine that wall costs $5
million. And it's very high It looks normal, there's nothing odd. Category. Again, not perfect. Ohh I think
I haven't selected actually. That's probably why. So what's nice about it is it
pulls over the sliced data. So if you have a lot of data you
can actually narrow it down to certain aspects. Getting that data still looks
normal. Actually right here. However,
the cost of the wall is quite high compared to other
categories. So it did analyze the data even
further. So that's great. One of the
things I'm thinking of adding here is selection of Prequalified statements. Please tell me what's the
highest value. Please tell me what's the lowest value. Please
give me again Power BI already does a lot of this, but I wanted
to see if ChatGPT could do it and as you can see, it definitely can.
I went back and I actually asked if ChatGPT. can analyze data and it can
identify patterns and generate the insights. So what I ended up
changing in my flow. To see if I can get additional
data and insights. Is. In the HTTP I've changed it to
say please identify patterns and generate insights for the
following data. I will admit this made a big
difference. It provided a lot more information. I tested this
on a service board and it actually gave me one through 5
the analysis of the data itself. I'd recommend trying it out. Hopefully it gives you as much
insights as it was giving me. Again, this is a Some Guy named
Chris. Have a great day.
