How to Edit Data in Power BI

Building dashboards in Power BI feels powerful, but your charts are only as good as the data powering them. If your source data is messy, riddled with typos, or not structured correctly, your final report will be inaccurate and misleading. This is where you need to roll up your sleeves and edit your data directly within Power BI.

This tutorial will guide you through the process of cleaning and transforming your data using Power BI's built-in Power Query Editor. We’ll cover the most common data editing tasks you'll need to turn raw, messy information into a clean dataset ready for professional analysis and reporting.

What is Power Query and Why Should You Use It?

Before creating a single chart in Power BI, your data passes through an incredibly powerful tool called the Power Query Editor. Think of it as a workshop for your data. It's not a spreadsheet like Excel, instead, it's a dedicated environment for data transformation. You don't change individual cells one by one. Instead, you apply transformation steps to entire columns or tables that can be repeated automatically every time you refresh your data.

So, why would you need to use it? Here are a few common scenarios:

• Cleaning Messy Data: Your data export might have typos, inconsistent formatting, or extra spaces that throw off your analysis.
• Removing Unnecessary Information: Your source file might contain dozens of columns, but you only need five for your report. Power Query lets you remove the noise.
• Restructuring Data: You might need to split a single column (like "Full Name") into two ("First Name" and "Last Name") or merge multiple columns into one.
• Changing Data Types: Sometimes Power BI misinterprets numbers as text or dates as plain text. This prevents you from performing calculations or creating time-based charts.
• Creating New Columns: You can add new columns based on calculations or conditional logic from existing data, like creating a "Profit" column by subtracting "Cost" from "Sales."

Learning to use the Power Query Editor is the difference between fighting your data to create a workable report and effortlessly building dynamic, accurate dashboards.

Getting Started: How to Open the Power Query Editor

Accessing the Power Query Editor is your first step. When you first load data into Power BI Desktop, you're presented with a navigator window. From here, you have two options: Load, which loads the data as-is, or Transform Data, which opens the Power Query Editor.

If you've already loaded your data, don't worry. You can access the Power Query Editor at any time from the main Power BI Desktop window.

Step 1: In the Home tab of the Power BI Desktop ribbon, look for the "Queries" section. Step 2: Click the Transform data button. This will launch a new window: the Power Query Editor.

When it opens, you'll see a few key areas:

• The Ribbon: At the top, you'll find tabs like Home, Transform, and Add Column, which contain all the tools for data manipulation.
• Queries Pane: On the left side, you'll see a list of all data tables (or "queries") you've loaded into your report.
• Data Preview: The central area shows a preview of the data in your currently selected table.
• Query Settings / Applied Steps: On the right side, there's a pane that records every single change you make. This is the most important feature, and we'll come back to it later.

Common Data Editing Tasks in Power BI

Once you're in the Power Query Editor, you can start cleaning and shaping your data. Here are the most fundamental tasks you’ll perform over and over again.

1. Changing Column Data Types

Accurate data types are essential for correct calculations and filtering. For example, if your "Sales" column is accidentally formatted as text, you won't be able to sum it. If your "Order Date" is seen as text, you can't create a monthly trendline.

How to do it:

1. In the data preview, look at the top of each column header. You'll see an icon representing the current data type (e.g., ABC for text, 123 for a whole number, a calendar for a date).
2. Click on the icon.
3. A dropdown menu will appear with a list of data types. Select the correct one for your column, such as Decimal Number for revenue, Whole Number for unit counts, or Date for time-series data.

Power BI is pretty good at guessing, but you should always double-check your most important columns, especially numbers and dates.

2. Editing Columns: Rename, Remove, and Reorder

Often, your source data isn't perfectly organized. Column names might be cryptic (e.g., "SLS_AMT_01") or you might have data you don't need.

• To Rename a Column: Double-click the column header and type the new name. Or, right-click the header and choose "Rename." Use clear, descriptive names like "Sales Amount" instead of codes.
• To Remove Columns: Select a column by clicking its header. To select multiple columns, hold down Ctrl while clicking. Right-click and choose "Remove Columns."
• To Reorder Columns: Drag a column header to its new position for easier reading.

3. Filtering Rows

You can filter data to include or exclude specific rows, helping remove errors or focus on segments.

How to do it:

1. Click the dropdown arrow on the right side of a column header.
2. Uncheck specific values you want to exclude.
3. For advanced filters, hover over "Text Filters," "Number Filters," or "Date Filters" for options like "Does Not Equal," "Begins With," "Is After," or "Is Between."

A common first step is filtering out (null) or (blank) values that skew your results.

4. Replacing Values

Typographical errors or inconsistencies are common. Power Query can standardize values:

1. Right-click the column header with the values.
2. Select Replace Values.
3. Enter the value to find (e.g., "Nwe York") and the replacement ("New York").
4. Click OK to replace all instances.

You can also use "Trim" (remove leading or trailing spaces) and "Clean" (remove non-printable characters) under the "Transform" tab.

5. Splitting Columns

To separate combined information, like splitting "Full Name" into "First Name" and "Last Name":

1. Select the column to split.
2. Go to the Home tab and click Split Column.
3. Choose By Delimiter.
4. Select the delimiter (space, comma, hyphen) that separates the data.
5. Confirm how to split (e.g., "At each occurrence").
6. Click OK, resulting in new columns.

6. Merging Columns

Combine multiple columns into one, useful for creating a "Location" field:

1. Select multiple columns while holding Ctrl.
2. Right-click and choose Merge Columns.
3. Pick a separator (comma, space, etc.).
4. Set the new column name and click OK.

7. Adding a Conditional Column

Create new categories based on logic, like grouping revenue:

1. Go to Add Column tab.
2. Click Conditional Column.
3. Define rules:

• Name: e.g., "Order Size"
• If: select a column (e.g., "Revenue"), operator (e.g., "is greater than"), value (e.g., 1000)
• Then: label (e.g., "Large")
• Else: label (e.g., "Small")

1. Click OK to produce the new column.

The Power of "Applied Steps"

The Applied Steps pane records all your transformations. Every change—renaming, filtering, type changing—is stored here as a step.

• Complete history: You can see your entire transformation process.
• Editable: Modify or delete steps by clicking the gear icon or the 'X'.
• Automated refresh: When you get new data and refresh, Power Query re-runs all steps automatically, keeping your process consistent.

This functionality is like recording a macro without coding: perform actions once, and they repeat with new data.

Final Thoughts

Mastering the Power Query Editor transforms Power BI from a visualization tool into a comprehensive data preparation environment. Cleaning, shaping, and enriching your data beforehand ensures your dashboards are accurate, reliable, and insightful.

While Power BI is powerful, connecting sources and performing transformations can involve manual effort. At Graphed, we streamline this process. Connect your platforms, and through simple, natural language commands—like "show weekly sales from Shopify for the US, excluding test orders"—we handle data connections, transformations, and visualizations automatically, delivering insights faster.