How to Do Data Analysis in Power BI

Transforming rows of raw data into a clear story about your business is a challenge, but Microsoft's Power BI makes that process manageable. It helps you connect to your data, clean it up, and build interactive dashboards that answer your most important questions. This tutorial will walk you through the fundamental steps of performing data analysis in Power BI, from fetching your first dataset to sharing your finished report.

Start by Connecting to Your Data

Before you can analyze anything, you need to bring your data into Power BI. Think of this as the foundation of your entire report. Power BI can connect to hundreds of different data sources, from a simple Excel spreadsheet on your desktop to a complex SQL database in the cloud.

For most users, the process is straightforward:

Open Power BI Desktop. In the "Home" tab on the ribbon, click the Get Data button.
A window will pop up showing an extensive list of data sources. Find and select the one you need. Popular choices include Excel Workbook, CSV, SQL Server, and Web.
Once you select your source, follow the prompts to navigate to your file or enter your server credentials.
Power BI will show you a preview of the tables in your data source. Select the tables you want to analyze and click Load. If your data looks messy, click Transform Data instead - which we'll cover in the next section.

Loading the data will import it into your Power BI file, creating a copy that you can now work with independently of the original source.

Step 2: Clean and Shape Your Data with Power Query

Real-world data is rarely perfect. It often contains typos, missing values, incorrect formatting, or extra columns you don't need. The Power Query Editor in Power BI is where you shape this messy data into a clean, usable format. You can access it by clicking Transform Data on the Home ribbon (or when you first load your data).

Here are a few common data cleaning tasks you can perform in Power Query:

Removing Columns or Rows: If your dataset has irrelevant columns bloating your report, simply right-click the column header and select Remove. You can also remove blank rows from the "Reduce Rows" menu.
Changing Data Types: Power BI is smart, but it sometimes guesses a column's data type incorrectly. For instance, a 'Date' column might be imported as text. You can fix this by clicking the icon in the column header (like "ABC" for text) and selecting the correct type (e.g., Date, Whole Number, Decimal Number). Proper data types are necessary for calculations and accurate reporting.
Splitting Columns: Have a 'Full Name' column that you need to break into 'First Name' and 'Last Name'? Select the column, go to the "Split Column" feature, and choose your delimiter (like a space).
Replacing Values: You can easily find and replace specific values to standardize entries. For example, if you have user data where the country is listed as both "USA" and "United States," you can replace all instances of "USA" with "United States" to make your analysis consistent.

The best feature of Power Query is the Applied Steps pane on the right. Every single transformation you make is recorded as a step. This means if you make a mistake, you can simply delete a step to undo it. It also means that when you refresh your data, Power BI will automatically re-apply all these same steps to the new data, automating your cleaning process entirely.

Step 3: Build Your Data Model

Once your data is clean, the next step is to create a data model. If you only have one table (like a single spreadsheet), you might be able to skip this. But as soon as you have multiple tables - like sales, products, and customer info - you need to tell Power BI how they relate to each other. This is an essential step that makes complex analysis possible.

In Power BI Desktop, you'll find the "Model" view on the left-hand navigation bar. Here, you'll see your tables represented as boxes.

Creating Relationships

Relationships link your tables together using a common column. For example, you might have a Sales table with a 'ProductID' column and a Products table that also has a 'ProductID' column. You can create a relationship by dragging the 'ProductID' from one table and dropping it onto the 'ProductID' of the other.

This simple action lets you do things like filter sales by product category or see which customers bought which products, even though that information lives in separate tables.

Creating DAX Calculations

DAX (Data Analysis Expressions) is Power BI's formula language. It’s like Excel formulas but supercharged for data analysis. With DAX, you create new information from your existing data.

There are two primary types of DAX calculations:

Calculated Columns: These calculations add a new column to one of your tables. You might use this to calculate something for each row. For example, if your sales table has 'Unit Price' and 'Order Quantity' columns, you could create a calculated column for total revenue per sale.
Measures: These are calculations that aggregate data, like summing a column, counting rows, or calculating an average. Measures respond to filters in your report dynamically. They are the backbone of most quantitative analysis in Power BI.
And a more complex example:

Learning DAX takes time, but starting with simple measures like SUM, COUNT, and AVERAGE will take your analysis a long way.

Step 4: Visualize Your Data in a Report

This is where your analysis comes to life. Head back to the "Report" view in Power BI, which provides a blank canvas for you to storyteller with data. Here's a quick overview of how to build visualizations:

On the right side, you'll see the Visualizations pane with icons for different chart types (bar charts, line charts, pie charts, maps, etc.) and a Fields pane that lists all your data tables and columns.
Create a visual: Select a chart type from the pane, and an empty visual will appear on your canvas. Alternatively, just drag a field from the Fields pane onto the canvas, and Power BI will choose a chart type for you.
Add data to the visual: With the visual selected, drag fields from your Fields pane into the wells under the Visualizations section. For a bar chart, you might drag a category like 'Product Name' to the Y-axis and your 'Total Revenue' measure to the X-axis.

Tips for Effective Visuals

Use the right chart for the job. Use line charts for showing trends over time, bar charts for comparing categories, maps for geographical data, and Card visuals for displaying a single, important number (like a key performance indicator or KPI).
Keep it clean. Avoid cluttering your report with too many colors or visuals shoved onto one page. Give your charts clear titles and ensure your labels are easy to read.
Make it interactive. Add slicers (visual filters that live on the report canvas) to allow your users to filter the entire report page by different categories, like date ranges or store locations. Clicking on an element in one chart will filter other charts on the page by default, a feature that's great for drilling down into details.

Step 5: Share and Collaborate

A report is useless if the people who need it can't see it. Once your masterpiece is complete in Power BI Desktop, the final step is to publish it to the Power BI Service (the cloud-based component of Power BI).

Click the Publish button on the Home ribbon in Power BI Desktop.
Select a workspace to publish your report to.
Once published, you can access your report via your web browser. You can then share it with colleagues by sending them a direct link, creating a public web embed, or packaging it within an app for broader distribution in your organization.

In the Power BI Service, you can also set up scheduled data refreshes, ensuring your reports are always showing the latest information without any manual effort from your side.

Final Thoughts

Working through these steps - connecting and cleaning data, building a data model, and creating visualizations - will equip you with the full set of skills needed to perform meaningful analysis in Power BI. While starting can feel intimidating, once you understand the workflow, you can build surprisingly powerful interactive reports that uncover valuable business insights.

Although Power BI is incredibly capable, setting everything up - especially cleaning data in Power Query and writing complex DAX measures - requires a dedication to learning new software. We created Graphed because we believe anyone on your team should be able to get answers from your data without first needing to become a data analyst. It lets you skip the technical setup by securely connecting your marketing and sales platforms (like Google Analytics, Shopify, and Salesforce), and builds real-time dashboards for you using simple, plain-English prompts. This frees you up to find insights instead of wrestling with formulas.