Power BI is a powerful data visualization and business intelligence tool that enables users to transform data into actionable insights. This guide will walk you through the process of importing data, creating compelling visualizations, and building comprehensive reports in Power BI. Whether you’re a beginner or looking to enhance your skills, this step-by-step guide will help you master Power BI and make the most of your data.
How To Import BI Data & Build Visual Reports
Step 1: Importing Data into Power BI
Before you can create visualizations, you need to import data into Power BI. Power BI supports a wide range of data sources, including Excel, CSV, SQL Server, and many cloud-based services.
Example:
Let’s say you have sales data in an Excel file that you want to analyze in Power BI.
1. Open Power BI Desktop.
2. Click on “Get Data” in the Home ribbon.
3. Select “Excel” from the list of data sources.
4. Browse to the location of your Excel file, select it, and click “Open”.
5. Choose the sheets or tables you want to import and click “Load”.
Step 2: Transforming and Cleaning Data
After importing, you might need to clean and transform the data to make it suitable for analysis. Power BI’s Query Editor is a powerful tool for this purpose.
Example:
Suppose your sales data has a column with combined date and time, but you only need the date.
1. In Power BI Desktop, go to the “Home” tab and click on “Transform Data”.
2. In the Query Editor, select the column with the date and time.
3. Right-click the column header and choose “Split Column” by Delimiter.
4. Choose the delimiter that separates the date and time, and click “OK”.
5. Now you have two separate columns for date and time. You can remove the time column if it’s not needed.
Step 3: Creating Visualizations
With your data imported and transformed, you can start creating visualizations.
Example:
To create a bar chart showing sales by product:
1. Click on the “Report” view in Power BI Desktop.
2. In the “Visualizations” pane, select the “Bar chart” icon.
3. Drag the “Product” field to the Axis area.
4. Drag the “Sales” field to the Values area.
5. Adjust the visualization properties as needed.
Step 4: Building Reports
A report is a collection of visualizations that provide insights into your data. You can arrange multiple visualizations on a report page to tell a story.
Example:
To create a sales report with multiple visualizations:
1. Add a new page in the Report view by clicking the “+” icon at the bottom of the window.
2. Create various visualizations such as pie charts, line charts, and maps, following the steps in Step 3.
3. Arrange the visualizations on the report page by dragging them into position.
4. Use the “Format” pane to customize the appearance of your report (e.g., background color, text size).
Step 5: Sharing and Publishing Reports
Once your report is ready, you can share it with others or publish it to the Power BI service.
Example:
To publish a report to the Power BI service:
1. Save your Power BI Desktop file.
2. Click on “Publish” in the Home ribbon.
3. Sign in to the Power BI service if prompted.
4. Choose the workspace where you want to publish the report and click “Select”.
Conclusion:
Mastering Power BI involves learning how to import data from various sources, clean and transform it, create meaningful visualizations, and build comprehensive reports. By following this step-by-step guide, you can turn raw data into insightful reports that drive decision-making and provide value to your organization. Remember, practice is key to becoming proficient in Power BI, so keep experimenting with different data sets and visualization types to enhance your skills.
Live Power BI Support Transcript Example:
Below is a live transcript of one of our Power BI experts helping a customer to import and work with data:
Full Conversation:
Customer: Hello?
Technician: Hello. Hi, this is Carl from Geeker. I got a request that you need help with something.
Customer: Yeah, well, the truth is I don’t really understand how to create content in Power BI.
Technician: Okay. So I’ve got, I’m trying to get this report that we use a data cube for to work in Power BI, but I don’t even know where to start. Um, so probably the easiest thing to do would be if you could share your screen and sort of talk me through what you’re trying to do. Let’s see if I can find the screen I want.
Customer: Gotcha. Okay. And then, so the first thing you have to do is figure out what data you need.
Technician: Yeah. So is it easier to show you what the report looks like?
Customer: Yeah, that’d probably be easier.
Technician: Okay. Get out of this. All right. So basically what it is, is it’s one, well I’ll say it’s one, it’s cookies. All right. So we sell cookies in our thing, in our business. I can’t see, if you’re screen sharing, I can’t see it.
Customer: Really? Yeah. Let me see. Nope. I’ll do it again. No. There we go. Okay. All right. All right. So basically all this is, is it’s pulling from the cube, as we call them. It pulls from these pivot tables in this cube to give me the sales per day of each week. And then I’ve got it pulling over here to week one. This is the sales that we did per day per store. The variance between the days, total unit growth. And then that store is week two. And then our directors and market managers, week one. Directors and market managers, week two. It’s pulling in data P5 so we can get the starting point of what we’re going against. Okay. And so basically that’s the tab that you would be showing people is this here. And then everything is pulling from all of these different pivot tables and cubes. So trying to figure out how to make this all work on Power BI is, yeah, it’s just not my thing. Well, really none of this is my thing. I’m learning as I go.
Technician: Right. So I guess the first question I would have is what are you trying to do differently in Power BI than what you’re already doing?
Customer: Really, I want to make it available for everyone, number one. Number two, I want it to be a little less cumbersome, I guess, for people to understand. Well, let’s say this tab here. Even though I tell them what week it is, tell them here’s the difference. These columns are the difference in what you did here. And this is per day here. People say, I don’t understand. I don’t know how you don’t understand that Saturday is 76 cookies less than you sold on this Saturday. Or not this Saturday, but your average. But it’s a constant thing. It’s also that my boss likes things on Power BI, which we just got a new boss. My last boss had nothing to do with that. But this boss is all, I like stuff on Power BI and nobody in our office knows how to do it. So I’m trying to figure it out. Also, if I could add a graph, how the RTOs are doing, market managers, the stores, those kinds of things. Or other things that I like to figure out. But right now, it’s just trying to learn how do you put anything on there that makes sense. And started with something that we did, created for a sheet. So maybe I can understand it more because understanding Excel, pivot tables and everything. I never dealt with them until I came to this job. And so I’m learning that as I go, as well as now trying to figure out Power BI.
Technician: Got it. Okay. So if you want to switch back to the Power BI tab. And again, I can’t see what you’re sharing because when you only share the one window, then I can only see that program.
Customer: Well, it’s still in the same window, but for some reason, every time I switch.
Technician: It’s not in the same window, though. You can switch from Excel to Power BI.
Customer: Gotcha. I should have clicked entire screen. I get it.
Technician: Yeah, it’s fine. I got three screens and I’m used to using Teams.
Customer: Oh, I got you. So I was trying to figure out which screen to show, but I didn’t realize I was doing window and not screen.
Technician: Gotcha, gotcha. So, yeah. So you would start by clicking import data from Excel. And then it will take a minute.
Customer: I would recommend importing just the raw data.
Technician: Okay. Just that one. Data P5, data P6. And then just those two you’re talking about?
Customer: If that’s what everything is built from, then yeah.
Technician: Well, you’ve got all your suggested tables down here.
Customer: Yeah, I don’t know which ones these are. I guess I labeled them the same damn thing or something similar. Because the region and the market are pulling from other ones, other tabs or pivot tables.
Technician: Well, yeah, that’s what I’m saying is I probably wouldn’t try to bring in stuff from pivot tables. Because that’s already a summary.
Customer: Right.
Technician: So what we really want to do is pull in the underlying data, wherever that’s coming from. And then it’s loading. Everything in Power BI is slow.
Customer: Yeah, and when you’re paying per minute, it kind of sucks, right?
Technician: Well, I think probably the best thing to do here will be to sort of get you comfortable with the general environment. And then you can work on some stuff. And then if you have more questions, you can come back. We won’t try to do the whole thing now.
Customer: Okay.
Technician: So go to the on the left-hand side. Well, sorry. This is your visualization tab. This is where you’re going to build the stuff that people look at. But we’re not quite ready for that. On the left-hand side, click on the second button. This is our data view.
Customer: Did it import anything?
Technician: That’s weird. Click that arrow underneath P5 to expand it.
Customer: And it did. That’s weird.
Technician: If you scroll over, let’s see if it imported anything.
Customer: No.
Technician: Okay. What if you go down to data P6?
Customer: Okay. It’s all in P6 but not P5 for some reason.
Technician: Yeah, that’s strange. Okay. So the first thing we should probably do is give those columns names.
Customer: Great. And you can probably get rid of the stuff at the top because you don’t really want those rows at the top.
Technician: Okay. Oh, my gosh. The other thing that’s annoying about Power BI is that slightly different versions will have buttons in different places.
Customer: Yeah, right. We don’t want to remove columns. We want to remove rows.
Technician: Let’s manage roles. I don’t want to manage roles.
Customer: Yeah, we don’t want to do that. If you right-click on the, I guess, because you scrolled all the way to the left already. If you right-click on that, does it give you any options with the row?
Technician: Delete.
Customer: Yeah. No, we don’t want to do that.
Technician: Well, I think you can probably hit undo.
Customer: Yeah. So. Okay. Maybe we should go into the transform data tab because I know the numbers were and everything and I was able to change stuff.
Technician: Right. Yeah, let’s do that. I’m not sure where that is.
Customer: Well, I’ve got to get back to the front.
Technician: Oh, yeah, here we go. Oh, God. Okay, so there’s data P5. Now it shows up. That’s weird.
Customer: Yeah, right. I’m able to.
Technician: Right. So we want to get rid of rows one through 10. So you can hit remove rows.
Customer: Yeah. Remove top and then. What is that? Top 10, right?
Technician: Yeah, that’s what I was. And.
Customer: I’m running into with because I actually did this earlier messing around trying to figure it out, but it’s like the way that it’s set up now is that we’re doing first week, second week, third week for this contest is four weeks long. So these are all days. So, like, this is Monday, you know, and then these other ones are Tuesday, Wednesday, and then you get back to Monday again. And it’s a. It’s a duplicate label.
Technician: Oh, so I would. I gotcha. I think what I would do is. We got to give them some sort of name like you call it Monday one.
Customer: All right, I’ll just call the next one Monday week too.
Technician: Right. I guess we can also remove the next three rows because we don’t really want those either.
Customer: What do you need here?
Technician: Yeah, because those aren’t actual data.
Customer: Okay.
Technician: Okay. And then you can rename the yeah, let’s do the first week.
Customer: Well, but those shouldn’t be week two and week three. Should they? This is also.
Technician: Yeah, it’s all equal. I just figured, hey, why not make this thing longer?
Customer: Exactly. Cool. And then you can just hit close and apply.
Technician: Okay. Okay. Okay. Okay. Okay. Okay. Okay. Okay. Okay. Okay. Okay. I think that was. That was data P five. We got to do the same thing to be six.
Customer: Well, we don’t do right now.
Technician: Okay. Okay, so let’s take a look at the P five data again. Just to make sure it looks.
Customer: Make sure it looks blank here, but it doesn’t and transform data.
Technician: Yeah, that’s where the heck are the numbers.
Customer: Okay. That’s very strange, but all right. So now you can come to the visualization tab.
Technician: I don’t know how to get there from here.
Customer: Oh, on the, on the left hand side. With those three things, the top one.
Technician: Yeah. So what you could do is you could draw a chart. Like, if you grab that top chart there.
Customer: Up here.
Technician: Yep. I think you can just click it and it’ll add it.
Customer: There you go. And then if you go to data P five. And click the arrow to expand.
Technician: And. Put market managers as the Y axis. And.
Customer: Oh, it really says there’s no data.
Technician: Yeah. Okay. That’s great. Close it and start over. Go back to the data sheet and try just hitting refresh.
Customer: And maybe that’ll do it. I don’t understand why it would show it to us in transform, but not.
Technician: I don’t know. It said that last time too.
Customer: Yep. It sure did. And the bad thing is it shows it in transform data, but not here. That’s weird. Very weird.
Technician: Okay. Yeah, I guess we have to transform six. And, and figure out that other weirdness. Later. So click on data P six and then click transform.
Customer: The thing that, and this is the piece of it, like. Is. The whole thing is about where you’re at versus P five.
Technician: I understand. This, like I said, I think that. Exploring you’ll be able to build those comparisons. This is mostly just aiming to, you know, Get over the first sort of hurdle of what, where is anything? And then we can go to transform data and then we can get rid of the first.
Customer: Yeah. Yeah. Cause like I can. I can do one thing. Like I could take that. You know, P six, I guess. And like you said, slide. Market manager’s over. It’ll show stores. It’ll show their numbers. But making. Different things show this is P five versus P six. And this is your growth. So. Both in units and sales per day.
Technician: Right. We’re having them talk to each other. I guess, for lack of a better term.
Customer: Right. So, okay. Yeah, then come down here to be six. I want to get rid of these stupid rows.
Technician: Yep. Okay. Okay. Oh, cause there were, cause there were blanks.
Customer: Yeah, but there wasn’t. Oh.
Technician: Okay. Okay. Okay. Okay. So then. If you go back to the.
Customer: Okay, that one works at least. Thank goodness. If you come back here.
Technician: Okay. And then you can see this coming from P five. No, no, sorry. In the visualization panel where it says, why. Then come to P six. And you can drag market manager to the Y axis. And then put P five average on the X axis.
Customer: Okay. And then. You don’t really want it to be count of P five average. You want it to be. If you click on the arrow next to. Where it says count of P five average. You can change it to.
Technician: Oh, no. That’s weird. Why is it doing that?
Customer: I don’t know. It must not have. I think it’s a number. Maybe.
Technician: It’d be better to close out. That’s not going to help it.
Customer: Okay. Come back to the data sheet. It must’ve brought it in as text.
Technician: Okay. Number. So go to transform data again. And then select that. Oh, yeah. Right. And then where it says data type, any. Change that to whole a whole number. And then hit close and apply.
Customer: So probably because the column started with. Stuff at the top of the spreadsheet. Oh, this is just text. So that was why it only let us do the count. Now, if you come back to your visualization. What you’ll see is. You can change from count to.
Technician: There we go. You can change like some.
Customer: Cool. And then you can. You know, resize your chart. To make it however big you want.
Technician: That’s the sort of simplest way to make graphics.
Customer: Yeah. You can add other stuff. Like you can make. You can add information for the color. Like here, the color is just a constant. But if you go to. On the visualization. On the right. On the right. On the right. On the right. Yeah, right. So if you go over one. To the. That one. Yep. I don’t know. Not that one. Nope. Okay. Click back on the first part. Yep. I guess it doesn’t let you do it on that kind of graph. Okay. Anyway, the one thing that you’ll notice as you. Play around with these graphs is when you click on. A bar like that. And so the one is highlighted. Yep. What that will do is that will filter. Everything on the visualization to only be. Ashley’s stuff. If you want to reset, you got to click somewhere else. You can change color here. Yeah, no, that’s not what I was. That’s. Right. You can do stuff like that there. That wasn’t what I was. Envisioning I was thinking like. Using color represent another variable. But I got you got you. But. Okay. And let’s just see, we can add another. X value. So if you want to come back to, yeah, click there. And then drag. Tuesday. Onto your X axis. Not the, not the Y axis, the X axis. Right. We can see it. It’s stacks. Tuesday on top, which isn’t really what we want. So let’s switch to. Maybe that third bar graph on top. Instead of the first one. There we go. So it’s showing. P five value and then the value for Tuesday of this week. Okay. And then you can just figure out what’s the best way to display this information. And, and build a graph to do that. So if I wanted, I mean. And obviously it’s. If I want to look at, so we did market manager here. Right. It’s probably not going to show a damn thing. If I didn’t want Tuesday. I want to market manager from P five. Right. That was the next thing I was going to say. Don’t do that yet. That’s not going to be helpful for us. You’re going to want to get. That didn’t work. Right. Because it doesn’t know how to connect those two things. So delete the top one. Okay. Now here’s what we do. To accomplish the kind of thing you were trying to do. Go to that third option. On the left-hand side. Right. This is your data model. What you want to do is. This is how you connect one table to another. Okay. So you can click manage relationships. Yeah. Here we go. And then hit new. And you can put. Right. Select that. And then your. Market manager and market manager. And then hit close. And then it’ll take it a minute. Because everything takes a minute. What it does is when it sees a market manager in P five. And in P six, it now knows those are the same person. Okay. So if you want to come back to your, well, it won’t work. Cause for some reason, the P five data isn’t loading. Okay. So if it were, you could drag. One of the sales days from P five. Onto the X axis and it would associate it with the appropriate. Managers. So. You could do that, but this is her, this is their total. Right. P five average. Interesting. Well, this is the sum of P five average, but it. How’s it the sum of P five. Yeah. It is, but we were doing P six. So I’m wondering how it’s P five. Because that was the. Because the column from P six was P five average. Oh, yeah. So. So that’s the average per day. But she did a. Well, you know what, let’s do this. Yeah. P five, right. Right. Yeah. So what you could do for a more direct comparison would be dragging Tuesday of week one. From P five and from P six. And those ought to be directly comparable. Yeah. It’s just interesting that it’s. Yeah. 507. Got to. I figure out the data because that’s not. Yeah, right