Before you can create stunning visualizations in Power BI, your data needs to be in the right shape. The Power Query Editor is your all-in-one workshop for cleaning, restructuring, and shaping your data.

With this tool, you can transform your raw data into the precise format needed for insightful reports and visualizations. This article will guide you through how you can edit queries in Power BI, empowering you to turn raw information into analysis-ready insights.

Power Query

Power Query is a data connectivity and data preparation tool that’s integrated into Excel and Power BI. It allows you to discover, connect, combine, and refine data across a wide variety of sources.

With Power Query, you can import data from different sources which include databases, local files, online services, webpages and other sources. These datasets, when imported, can then be transformed and shaped so that the data meets your specific needs.

There are different places where you can access the Power Query Editor in Power BI.

When you go to the Get data option in the Home tab, on the Blank query option. After this, the Power Query editor window will open.

Also, you will open the Power Query editor window when you try to import a dataset and you click on the Transform Data option.

If you have already imported the dataset but still want to make some transformations on the dataset, you can go to the Data tab, right-click on any table name and select Edit query to go to the Power Query editor.

This option is also available when you go to the Model view and select the three horizontal dots on a table.

The Power Query Interface

The Power Query editor offers a well-organized workspace to streamline your data preparation tasks. Its layout is designed to provide clear navigation and visibility into your transformation process.

Here’s a tour of the Power Query Editor detailing how it simplifies complex data manipulation and showing some of the things you can do with it.

1. Ribbons

The Power Query ribbon acts as the command centre for data transformation. It provides a collection of tabs, each containing buttons and options specific to different stages of the data-wrangling process.

While some redundancy might exist across tabs depending on the product you’re using (Excel or Power BI), these buttons ultimately offer a convenient one-click approach to applying the transformations and actions you need to get your data ready for analysis.

Here’s a summary of the things you can do in each of the tabs you will find in the Power Query interface.

File tab

The File tab in Power Query is the control centre for managing your data transformations, ensuring that changes made to your data are applied. In the File tab, you will find options such as Close, Close & Apply, Save and Save as.

Home Tab

Think of the Home tab as your starting point for any data exploration and transformation task. Here you’ll find options to:

• Connect to Data: This button launches a menu where you can choose from a wide range of data sources, like Excel files, databases, and web pages.
• Refresh Preview: Once you’ve connected to your data, use this button to retrieve the latest information.
• Close & Apply: This finalizes your data transformations and loads the transformed data back into your Excel or Power BI environment.

The Home tab also offers additional functionalities like managing queries, data source settings and navigating between them.

Transform Tab

This tab provides a vast array of tools to clean, reshape, and organize your data. Some of the common actions you can perform are:

• Filter Rows: Keep only specific rows that meet your criteria.
• Remove Duplicates: Eliminate redundant entries in your data.
• Split Columns: Divide a single column into multiple columns based on delimiters or patterns.
• Merge Columns: Combine multiple columns into one.
• Change Data Type: Ensure your data is in the correct format (e.g., text, number, date).
• Merge Queries: Combine data from multiple tables into a single, unified table.

The transform tab has a variety of other transformation options which you can explore.

Add Columns Tab

This tab focuses on creating new columns with calculations based on your existing data. Here’s what you can do:

• Custom Columns: Use formulas and functions to generate new columns containing calculated values.
• Columns From Examples: This option allows you to add new columns to your data model by giving example values. You can create new column examples from a selection or input based on all existing columns.
• Date and Time Manipulation: Extract specific parts from date and time data (e.g., year, month, day).

View Tab

The View tab allows you to personalize your Power Query experience:

• Show/Hide Panes: Control the visibility of different panes in the workspace, like the formula bar or query settings.
• Advanced Editor: Access a code editor for more complex data manipulation tasks using M, Power Query’s formula language.

• Help Tab: It provides you with resources and assistance when working with this powerful data transformation tool. It offers resources for when you get stuck. You can get online help, documentation, and training videos among other things.

2. Query Pane

The query pane is where you get a list of all the datasets or queries imported into Power Query. It displays a list of all the queries you’ve created within your Power BI project. Each query represents a specific data connection and its associated transformation steps.

Also, the query pane allows you to perform various actions on your queries such as renaming, reordering, hiding/unhiding, duplicating, grouping and deleting queries. In Power Query, queries refer to tables of datasets.

3. Formula Bar

The formula bar in Power Query is a crucial tool that allows you to see and edit the code that represents each of your data transformations. The formula bar is typically found directly above the data preview area in the Power Query Editor.

The primary function of the formula bar is to display the M language formula that corresponds to the currently selected transformation step. M is the powerful language underlying Power Query’s actions.

Additionally, you can directly edit the M code within the formula bar to make more precise or complex changes to your transformations.

4. Query Settings

The Query Settings pane in Power Query is a crucial area where you can control properties, review transformation steps, and fine-tune how your queries interact with your Power BI project.

The Query Settings pane is usually found on the right side of the Power Query Editor.

Here’s a breakdown of its components and functions:

• Name

This is where you can view and edit the name of your query. This name will also be used for the table generated within your Power BI data model.

• Applied Steps

This section is the heart of the Query Settings pane. It displays a chronological list of all the transformation steps you’ve applied to your data. Clicking on a step will update the data preview to reflect the state of your data after that particular step. You can rearrange steps by dragging and dropping, deleting steps, or right-clicking for more options like inserting new steps.

• Properties

This section displays additional details and options related to the currently selected query or step, depending on the context. Options might include data load settings (e.g., enabling/disabling load to Excel or the data model), privacy levels for sensitive data sources, and description fields to add comments or documentation to your query.

5. Preview Window

In this section, you get to see the rows and columns present in a table. By default, Power Query shows the first 1000 rows, but you can make it display all the rows of data.

Most of the options in the ribbons, particularly the options in the Transform tab are present in this preview window when you right-click on a column header. You can add data quality metrics to the preview window from the View menu.

These metrics display information such as column quality, the number of unique and distinct values, and general column stats such as the number of blanks, distribution of data and so on.

6. The Status Bar

This section displays information such as the number of the total number of rows and columns in the model, and the last time the query was refreshed.

Benefits of Power Query Editing

Editing data in Power Query offers several significant benefits that streamline the data preparation process and enhance the quality and usability of your datasets. Here are some key advantages.

Data Cleansing

Power Query allows you to clean and standardize your data easily. You can remove duplicates, correct errors, handle missing values and apply various transformations to ensure data consistency and accuracy.

By eliminating inconsistencies and inaccuracies early in the process, you enhance the reliability of your analysis and reporting.

Data Transformation

With Power Query, you can perform a wide range of data transformation operations without writing complex code. You can reshape, pivot, unpivot, split, merge, and combine data tables effortlessly.

These transformation capabilities enable you to structure your data in a way that’s conducive to your analysis requirements, saving time and effort in data preparation.

Data Integration

Power Query allows you to integrate data from multiple sources seamlessly. Whether your data resides in Excel spreadsheets, relational databases, cloud services, or web sources, Power Query enables you to connect to and consolidate data from disparate sources into a single dataset.

This integration capability simplifies the data acquisition process and provides a holistic view of your data for analysis.

Data Enrichment

Power Query enables you to enrich your datasets by adding calculated columns, custom calculations, or applying business rules. You can create new columns based on existing data, perform mathematical calculations, or derive insights through conditional logic.

This enrichment enhances the depth and context of your data, facilitating more insightful analysis and decision-making.

Data Reusability

By editing data in Power Query, you create a reproducible and auditable data preparation workflow. The steps you apply to clean, transform, and enrich your data are recorded and can be easily replicated or modified. This reusability ensures consistency in data preparation processes across different datasets or analysis scenarios, promoting efficiency and reliability.

Data Performance Optimization

Power Query includes features to optimize data performance, such as data load settings and query folding. You can specify how data is loaded into your analysis tool, whether in-memory or as a direct query, to balance performance and resource utilization.

Additionally, Power Query leverages query folding to push data transformation operations back to the data source whenever possible, optimizing performance and reducing data transfer.

Conclusions

By mastering the art of editing queries in Power BI, you elevate your data analysis game to a whole new level. Not only does Power Query save you time and reduce errors, but it also unlocks new possibilities for shaping your data to answer complex business questions.

Whether you’re cleaning messy spreadsheets, combining multiple data sources, or creating sophisticated calculations, Power Query is the transformative tool that will streamline your data workflows and empower you to extract greater insights.