How to Edit Query in Power BI
Jumping into Power BI means you’re ready to turn raw data into sleek, insightful reports. But before you can build those eye-catching visuals, your data needs to be clean, organized, and properly formatted. This is where Power BI’s most powerful feature comes into play: The Power Query Editor. This article is your guide to mastering the editor to shape any dataset to fit your exact needs.
What is the Power Query Editor?
Think of the Power Query Editor as a dedicated workshop for your data. It's an environment within Power BI specifically designed for what's known as the "Transform" stage of data analysis. Before data is loaded into your Power BI report for visualization (the "Load" stage), it must be extracted and then transformed.
This transformation process can include tasks like:
Removing unwanted columns or rows.
Cleaning up text and correcting errors.
Changing data types (e.g., from text to numbers or dates).
Splitting one column into several.
Creating new calculated or conditional columns.
Essentially, any change you need to make to the structure or content of your data happens here. Every action you take is saved as a query. Learning how to edit that query is the key to becoming truly proficient with Power BI.
How to Open the Power Query Editor
You can get to the Power Query Editor in a couple of simple ways, depending on where you are in your reporting process.
Method 1: When loading new dataWhen you first connect to a data source (like an Excel file or a database) using the "Get Data" button, a navigator window appears, showing a preview of the tables. At the bottom right of this window, instead of clicking "Load," click "Transform Data."
This is the best practice for any new data source, as it takes you directly to the editor to clean your data before loading it into your report.
Method 2: With data already loadedIf you've already loaded data into Power BI Desktop, you can access the editor at any time. In the main Power BI Desktop window, go to the "Home" tab. In the "Queries" section, click the "Transform Data" button. This will open the Power Query Editor with all of your loaded data tables visible in the Queries pane on the left.
Understanding the Power Query Editor Interface
When you first open the editor, the screen is broken down into four main areas. Getting familiar with them makes navigation much easier.
The Ribbon: Just like other Microsoft products, the top ribbon holds all of your tools, grouped into tabs like "Home," "Transform," "Add Column," and "View." This is where you'll find the buttons to perform all your data transformations.
Queries Pane: On the left side, this pane lists every data source or table you've connected to. You can click on any query here to view its data in the preview pane and edit its specific transformation steps.
Data Preview: The large central area displays a preview of your selected query's data. This view lets you see the immediate effect of any transformations you apply.
Query Settings Pane: This is arguably the most important section for editing your queries. Located on the right, it has two key parts: "Properties," where you can rename your query, and "Applied Steps," an ordered list of every transformation applied to the data. This list is the core of editing a query.
Key Techniques for Editing Queries
Let's walk through the most common data transformation tasks you'll perform. Each of these actions will be recorded in the "Applied Steps" pane, which is how you edit, remove, or modify your query over time.
Cleaning and Shaping Your Data
Removing Columns: Often, your source data has columns you don't need. To remove one or more, simply select them (hold Ctrl to select multiple), right-click the column header, and choose "Remove."
Filtering Rows: Just like in Excel, you can filter rows based on their values. Click the drop-down arrow on any column header to see a list of values you can filter by. The "Text Filters," "Number Filters," or "Date Filters" options provide more advanced logic (e.g., "does not contain," "is greater than," "is in the previous week").
Changing Data Types: Power BI is pretty good at guessing data types, but it's not always perfect. Having the correct type (like "Whole Number," "Decimal Number," or "Date") is crucial for accurate calculations. To change a type, click the icon on the left of the column header (e.g., ABC for text, 123 for number) and select the correct type from the list.
Renaming Columns: Give your columns clear, easy-to-understand names. Double-click the column header and type a new name, or right-click and select "Rename."
Replacing Values: If you spot typos or data inconsistencies (e.g., "NY" versus "New York"), you can easily standardize them. Right-click on a column you want to change, select "Replace Values," and enter the value to find and the value to replace it with.
Creating and Modifying Columns
Splitting Columns: Have a column with "First Name Last Name"? You can easily split it. Select the column, go to the "Transform" tab, and click "Split Column." You can split by a delimiter (like a space or comma), by number of characters, and more.
Adding a Conditional Column: This is an incredibly useful feature. It lets you create a new column with values based on rules or conditions applied to other columns. For example, based on an "Order Amount" column, you could create a "Deal Size" column. From the "Add Column" tab, select "Conditional Column." In the dialog box, you might set the logic: If [Order Amount] is greater than 1000, then output "Large Deal," else output "Small Deal."
The Heart of Query Editing: The "Applied Steps" Pane
Every single action you take in the editor - removing a column, filtering rows, changing a data type - is automatically recorded as a unique step in the "Applied Steps" list. This list is your recipe for the data. It's read from top to bottom, one transformation at a time.
This is where real query editing happens:
Reviewing Transformations: You can click on any step in the list to see what your data looked like at that exact point in the process. This is fantastic for troubleshooting and understanding how your data is being shaped.
Deleting a Step: Did you make a mistake? Just click the 'X' next to the step name in the list, and it's like that transformation never happened. The list will adjust accordingly.
Reordering Steps: Sometimes, the order of operations matters. You can drag and drop steps in the list to reorder them, but be careful - changing the order can break later steps if they depend on a step you moved.
Editing a Step: Many steps have configurable options. For example, if you filtered rows, you'll see a small gear icon ⚙️ appear next to that step in the list. Clicking it will reopen the filtering dialog box, allowing you to change your filter criteria without having to delete the step and start over. This is a core part of editing a query.
How to Change a File Path or Server (Editing the source query)
One of the most common "edits" people need to make is updating the data source itself. Maybe an Excel file you were connected to moved to a new folder, or you're moving from a test database to a production server.
Fixing this is easy:
In the "Applied Steps" pane, find the very first step, which is always labeled "Source."
Click the gear icon ⚙️ next to the step.
A dialog box will appear, allowing you to browse for a new file path or enter new server details.
Click "OK," and Power BI will update the source for that query, keeping all the subsequent transformation steps intact. Success! Your query now points to the new data.
A Quick Look at the Advanced Editor (M Code)
Behind the user-friendly interface of Power Query, there lies a scripting language called M. Every click, filter, and transformation you make in the query editor is actually generating lines of M code in the background.
You can view this code by clicking the "Advanced Editor" button on the "Home" tab. For most users, editing M code directly isn't necessary. However, knowing it's there is useful. Sometimes, it's easier to make a tiny change (like updating a file path) directly in the code than by navigating through menus.
Don't be intimidated by the M code. Just remember that it's what's powering your transformations, and as you get more comfortable, you might find yourself tweaking it for more advanced custom transformations that aren't possible through the graphical interface alone.
Best Practices Checklist
Name Your Queries Clearly: Instead of leaving names like "Table 1," rename your queries to something descriptive like "Sales Data 2024" or "Marketing Leads." You can do this in the Properties section of the Query Settings pane.
Filter and Remove Early: Start by removing unnecessary columns and filtering out irrelevant rows. This reduces the amount of data your computer has to process, making subsequent steps run faster and improving your report's overall performance.
Verify Data Types: Before closing the query editor, do a quick sanity check of each column's data type.
Don’t Bring Everything in at Once: Be selective about the data you pull into your visual reports. The less you have to filter and remove with query transformations, the better. Good planning makes data preparation much easier.
Final Thoughts
The Power Query Editor is the true engine of Power BI, allowing you to transform messy, complex data into a clean, reliable foundation for your reports and analysis. By understanding how to access the editor, apply transformations, and edit those steps, you gain complete control over how your data is structured and presented.
While Power BI offers incredible control, we realize that the process of connecting sources, cleaning data, and transforming it can involve a steep learning curve and take up hours every week. We built Graphed to simplify this entire workflow. By connecting your data sources and asking for what you need in plain English - like "create a report showing sessions versus sales by campaign from Google Analytics and Shopify" - our A.I. data analyst handles all the transformations and dashboard creation for you, turning hours of configuration into a 30-second conversation.