Transcription
Power BI now integrates with Claude. It is easy to connect. I'm going to show you how to do it in this video. I'm also going to show you seven prompts that are game-changing. I'm going to demo it for you.
Claude is an AI engine. You should know what that is. It's a large language model. We can plug it into Power BI now and it will create every single measure. It's going to organize the model. It's going to create definitions. It's going to write all the metadata. It's going to analyze the model to determine all the data types and correct them automatically, as well as many other things. The possibilities are really going to be endless with this one. So, we're going to dive in right now.
The beginning of this video is going to show you all the value. The end of the video will be the five-minute process for how to connect Power BI to Claude. The end has the connections, the beginning has the value. You want to keep up to date with this, hit the subscribe button. More content comes for sure. Let's dive into Claude Power BI. Let's go.
All right, here we go. I'm going to start by showing you the end product and I'm going to put prompts in here as well and you're going to see. So, what you do, what the final thing is is you're going to have open on your desktop computer any Power BI model you want. I have one called MCP clog uh claude model context protocol and uh Claude Claude's open on the right-hand side. So, first thing I'm going to do right now, I'm just going to say connect. Let's see here. Connect to the open my keyboard. That's hilarious. Open Power BI. That's funny. Power BI desktop file. Let's look at this. Okay, Claude's going. It's going to connect to this model and he's going to help me connect. There you go. So, there might be some prompts that come up as well for permission, but it's connected. And again, I'm going to show you how to connect to this. But first, I'm going to show you the power of this thing. So, there you go. Claude's connected to this model. It can do all kinds of things. Ask it. Ask it yourself.
But what I'm going to do first, just to show you the craziness of this thing, is I'm going to start by saying this. So if we look in this model, we can see that there's a fact table uh centered around different dimensions, standard snowflake schema and or star schema. And it's going to rename everything for me. It's going to clean this sucker up, but there's no calculation table. So I'm going to start by saying that again. There's amount here. So I'm just going to tell Claude, here's a prompt I typed up. Create a new empty table to hold calculations. Create an aggregated amount. The words keep going. I'll put these prompts in the description. Everything you see here will be in the description, so you can click into that and get it. But watch this. I'm going to run this. You'll see the prompt up here. Create an empty table to hold calculations. Create an aggregate amount calc for every sales status value returned or sold. Create a net sales calc. And also create a comprehensive comprehensive group uh of monthly time intelligence measures for all measures created. And what we're going to do here, it's going to just connect. It's going to start doing things. You're going to see it show up on the on the side over here. I mean, this is going to change the way we code. This is going to change the way we create. And so, as it's processing, I'll probably skip ahead sporadically so you don't have to sit here and wait for it to process. But know that it only takes uh typically, you know, 20 30 seconds. So, let's go.
All right, we're back. It just processed. And now you can see all measures have been created successfully. Let me verify the results. There's a new table called measures. You'll see there's a star here. We'll take a look at that. create as a calculation table to hold all the measures. Uh hidden the placeholder column and now it's showing me what it created. Amount returned, amount sold, net sales, time intelligence, month of date, previous month, all this stuff. Four of them for everything. We're talking some serious calculations here. They're all properly formatted. All there. And so now if we just look at our model, hit refresh. Now look at that. Measures, they're all there. Are they even right? Let's take a look. I'll just pick one. will say the amount sold, the amount returned, and the net sales. And again, just looking at the data first, but you can see if we were to zoom in on this sucker, those numbers are correct. Uh, boom, boom, boom, boom, boom, boom, boom. It all checks out. So, okay, you can create measures really easily.
Well, let's take this thing up a notch. So, when we're looking at this, uh, now there's a ton of fields in this model. This is how sometimes things look, not organized, not named right. So next step, let's use this prompt here and analyze. Boom. I'll run this thing. We're going to analyze my model's naming conventions and suggest renames to ensure consistency. What this is going to do typically as a developer, you'd go through, you'd click everything, you'd rename it, and you'd make sure you follow the same pattern. You might make mistakes. Claude, it's going to analyze this. It's going to go through all these things. It's going to tell me what it wants to rename, and we're going to do it. Let's fast forward.
We're back. So, you can see the model's processing now. It's just analyzed my entire model and it's saying there's some inconsistencies. You have mixed prefixing styles. Some tables start with a zero, some start with underscore. There's inconsistent casing. The purpose is unclear. Naming issues. ID versus all this stuff. It just came up with it for me. It's going to rename everything. It's telling me what it wants to do. And now I'm gonna say uh uh I'm gonna say rename every do it all. Rename. My keyboard is being so weird. Rename everything. Boom. Now what this is going to do, we'll fast forward as well.
We're back. Rename everything. Check this out. It renamed every single table. Phase one. Phase two, updated every measure with new table references. Phase three, renamed all the fact sales, all the dim customer fields, all the dim store fields, all the dim product fields, all the dim date fields. Uh, it verified everything, complete summary here. Uh, you can show now it updated 28 column names making consistency across everything. Uh, this is crazy. And now you can see it achieved what it did. What do you want to do next? Well, check this out. This one's even more mind-boggling.
So, you can think of, okay, we have all this all these fields here. It's not very organized. How about we just simply prompt it to do this? Add display folders to organize fields better. Hit enter. Fast forward.
All right, we are back. This is incredible. So, it went through four, six phases. It organized all the fact dim, every single dim table, all the measure table. And look at this. Like that. It in it put everything in folders. Uh it even nested folders. the measures now instead of having all this giant stuff, base measures, time intelligence, mount return, it's it's incredible. Uh, and now it will tell me what it did. But let's keep going even further. We're going to do crazy cool stuff, generating dictionaries, all kinds of things.
So, let's keep doing uh another basic blocking tackling that people do as a best practice is after you create a model, you want to hide the ID columns. You want to hide things that aren't uh aren't really usable by the end consumers to make the model simpler. And look at this. Again, this is flowing here. All these things are now organized. This would take time. No more time for that. It says what it did. Let's start with this next prompt. I'm gonna get this guy over here. This next one. Boom. Hide all foreign key columns. We don't need to see them. We don't need to see these. And so, typically, I'd go through every folder, look for them, hide them. Fast forward.
And we're back. So, I told it hide all the foreign key columns, all these IDs. we don't need them. Uh, it said, "Cool. I'm going to do that for you." It went through the first step. Second step, it checked things. Some were hidden, some aren't. Verified user friendly names. Give me a whole summary. It shows me what it hid, what it took care of for me. Totally clean this thing up. Even nested in folders. Uh, and it's still letting me know what it did. What if we go see what that looks like? What a good experience. Now, you come get a product. There's no keys. Everything's organized. I mean, give me a break. This is incredible.
But let's go even further. This is a step that I never do because I just dread it. It's that every single table when you want to make a true data dictionary, you can populate a description for everything. Guess what? I've never done it because I never want to go type all that stuff in. How about I take five seconds and just prompt this thing and have it do it for me. So, check this out right here. Boom. Add descriptions to all measures, columns, and tables to clearly explain their purpose and explain the logic behind the DAX code in simple, understandable terms. This is going to generate for us the metadata in our model that then we can use in a data dictionary, which I'll do in the next step. But check this out. Right now, all these things are blank. The description is blank. It doesn't talk about nothing here, right? Okay, let's see what happens. Fast forward.
And we're back. Okay, now this is wild. Look at this. So, uh, it added descriptions to every field measure, the description, the synonym, everything. So, let's look. It went to first, uh, the facts, then the dims, and then, uh, every store, dim date. You can see the messages. But just so you get an idea, like look at this. Uh, now when you hover over a calculation well the month over month the whole description is there for every single thing it uses divide it shows what it does but then even better for AI usage it's now put in the synonyms uh for everything as well. So if we say we pick gender, gender, age, month, it's all month, name, year, month, all these things. It's all populated.
Let's keep going even more. Another thing that happens is you have to create all these hierarchies, date hierarchies, product hierarchies. Again, this is now showing everything it created. All this documentation. No more documenting for us. Claude will do it. Let's check this out now. So uh we are going to ask it now that we have all these definitions. Hey, create user hierarchies for drill down navigation. Do this for me. Figure out what what's up. Let's see it. Fast forward.
All right. So, this is processed again. I prompted it. I just said create user hierarchies for drill down navigation. Uh it went through it created a product hierarchy, a store hierarchy, an ownership hierarchy, a date hierarchy, uh a date hierarchy, uh well this is more details of it, a create a price hierarchy. But then what it does is it goes through and it gives a summary of every hierarchy. Here's what they are. Here's what they consist of. You can see now in the model hierarchies, hierarchies, hierarchies all created. It shows the levels, why they're there, why why they're there, how that to use them. It even gives tips. Here's how to use a hierarchy. It gives a summary. Uh it's incredible. It's creating all this stuff. Here's reporting scenarios, the whole thing. It now has official professional grade hierarchies.
Here's another thing. uh as we ingest data uh sometimes we don't optimize the power query that can make a huge deal with compression and the size of your data set. So uh this is another really good prompt right here where we're going to say this this boom. Hey Claude, analyze my data types and optimize them for better compression and performance. Change them as needed. It's going to go through and do this. Let's fast forward and we're back.
All right, look at this. So, optimize my model. It's going to say you got it. Let me analyze it. So, first it did an analysis and it said you've got some issues. There's issues with the sales table, the dim store, the product, the date. It's going to look through everything. I went through and I was just kind of observing the code. It goes through and it optimizes all those. It verifies the changes. And now, uh, the optimization's complete. It changed nine columns. What did it change? It's going to tell you it's changed things from sto like everything from before the impact it shows high impact the performance of joining of memory compression. Uh all these things now are improved automatically with a simple prompt. Uh look at this performance improvements. Boom. It listed out 50% up to 40% improvement on query performance. Two to threex faster on the joins. Uh best practices. This is incredible. Uh applies all this. It gives me the full breakdown of every data type now. What's correct? What's updated? What's optimized? Expected results. The model size just reduced 15%. Like I mean that's insane. Uh important notes. Refresh required. You can see over here it says hey refresh required. It knows what's up. Let's hit it. And look all error is resolved. It's just doing it. It's insane.
This is the final prompt that I think is just clutch. Now, when you have an enterprise semantic model, you need to create a data dictionary. This is painstaking. I mean, takes days. You've got to populate a giant PDF to share with the whole entire company, the enterprise, defining everything, showing them how to use it. No more. Let's do this prompt right here. Paste this sucker in here. Hit enter. Generate a markdown document that provides complete professional documentation for a Power BI semantic model, including a data dictionary. Use a simple mermaid diagram to illustrate the table relationships. Document each measure including the DAX code and a description of the business logic of the of the business logic using business-friendly names, etc., etc. Make it for me and then share it. Let's fast forward.
And look at this. We're done. I'll even make this bigger here for a second. So, check this out. Uh, the massive process of documenting something. Well, no more. It I said make the data dictionary. It went through it fully created it. And not only did it do that, it documented everything. I mean, look at this. The semantic model documentation, table of contents, everything, links, an executive summary, the model diagram. You want to talk about data governance, every field, the description, how it's working, the customers, the tables. Uh, it's it's got to be 60 pages of data right there. This would take a team of analysts days to do. Now you can create this. People can search it. You can save it as a PDF. You can download it. Whatever you want to do, uh, publish artifact, you know, you have it. It's incredible. Let's show you now how to build this stuff. Let's go.
All right. Now, we're to the good part of how do you install this? I'm going to show you how to set this up in like five minutes. It's extremely simple. Again, I'll put these links in the description, too, so you can reference them. If you want to get Microsoft's overview of the MCP uh model context protocol, you can go to their blog. Uh they have a GitHub that has more detail. Um, one thing to note is check this out because it does give example prompts uh down here at the bottom, but as well as some of the detail we're going through, but it's actually their documentation doesn't show you how to do it with Claude. So, you have to do something different. So, I'm going to talk you through the steps.
Okay, the first thing you're going to need to do is download Visual Studio Code. If you do not have this installed, download it. Uh download it, install it, come back to the video. Uh after you have that installed, there's another link. You can install the Power BI modeling MCP server. Again, this is very simple to do. You can do the link in the video or if you are in uh Visual Studio if you're not familiar with it. All you need to do on the lefth hand side go to extensions and you'll see this search bar search for Power BI modeling MCP server. If you click it, it will show you some things here as well. But then from this point, you can just simply install it. I have it installed already. Install it. After you do that, come on back to this video and we're going to keep on going.
So now the next thing you need to do in order to leverage Claude, you have to install the the Claude locally. Download the Windows version. So go to this link that I put in the description, bring Claude to your desktop, simply download it, and you're set.
Okay, so now what do we do? Here's the magic sauce for getting this thing to work. Okay, you're going to want to do this first in you're going to have two folders and I'll put the links. What you're going to want to do, get from the descriptions of uh in this video. Put this into your Windows folder. Just paste it. It will say user profile VS Code extensions. It's going to automatically go to where that is. It's going to show up down here for you. You should then be able to click it. After you click that, this you're going to find the folder that says Power BI modeling MCP. Go into that, you're then going to go to server and you found it. Boom. Power BI modeling MCP right here. Rightclick this thing, copy the path, put it in a notebook or something. You're going to need it later. Uh we'll come back to that. All right. So, I'm just doing that myself. Cool.
So, now the next step, you have Claw Desktop open. uh go into the lefth hand side, go down to the bottom, go to settings. In settings, there's a developer tab. You're going to see mine is connected to Power BI modeling MCP. It's running. It's set up. It's it's working. You're going to edit this. You won't see this screen. So, what you're going to do is you're going to click edit config on your screen. It's going to pop up this file. And in this case, it's going to navigate to the other window I showed, but this cloud desktop config. Here's the special sauce in the code in the video, the description. You can get this code, but just open this up, edit with Notepad, Notepad++, whatever you want to do, and this pops up. It will be blank when you get it. Um, what all you got to do is paste the code that I put into there into your uh into your view there. One important thing is that when you paste your path, you're going to put it right here in the command section. and you'll see it commented out in my thing in the description. It will have single slashes. You need to change all of them to double slashes. That's the trick. If you don't do that, it won't work. Uh after you do that, uh come back to Claude, shut it down. Shut down your whole entire computer. Restart everything. When you boot it back up, you should be able to open Claude and you will go to this screen. You'll see that this Power BI modeling MCP should be running and you are all set. That's how you do it. If you get an error, copy the error, put it into cloud. It will tell you how to resolve it, but you should be all squared up. Again, then that first prompt you're going to do is uh connect to the open Power BI desktop file. And all you'll want to do at that point in time is make sure you have a Power BI desktop file open and it will connect to it. It's incredibly awesome. And then you could even in the prompts search what are the best prompts? What are the top 25 best prompts? It's going to give you all kinds of stuff. You're going to love it. Uh enjoy. Take care. Bye.