Power BI Interview Questions
Power Query Editor
Table of Contents
May 22, 2024
Lokesh Paliwal – Data Analyst
What is Power Query Editor?
It’s a data transformation/data cleaning tool that uses M language in the background to record every action. It enables users to transform data using a user-friendly interface and create reusable data.
Features in Power Query Editor
| S. No. | Transformation | Description |
|---|---|---|
| 1 | Filter | Exclude rows or columns from the dataset based on specified criteria, such as value ranges or text patterns. |
| 2 | Sort | Arrange rows in ascending or descending order based on one or more columns. |
| 3 | Group by | Group rows together based on shared values in one or more columns, allowing for aggregation of grouped data. |
| 4 | Pivot | Rotate rows into columns based on unique values in a specified column, creating a pivot table-like structure. |
| 5 | Unpivot | Transform columns into rows, converting a pivot table-like structure back into a tabular format. |
| 6 | Remove Duplicates | Eliminate duplicate rows from the dataset, keeping only unique records. |
| 7 | Replace Values | Substitute specific values in the dataset with alternative values or expressions. |
| 8 | Split Column | Divide a single column into multiple columns based on delimiter characters or positions within the text. |
| 9 | Merge Columns | Combine two or more columns into a single column, concatenating their values together. |
| 10 | Add Custom Columns | Create a new column based on custom expressions or calculations using existing column values. |
| 11 | Extract Text | Extract substrings or patterns from text values in a column using regular expressions or predefined functions. |
| 12 | Aggregate | Calculate summary statistics, such as sums, averages, counts, or maximum/minimum values, for grouped data. |
| 13 | Data Type Conversation | Convert the data type of columns to ensure compatibility with analysis tools or downstream processes. |
| 14 | Rename | Change the names of columns or tables to improve clarity and consistency in the dataset. |
| 15 | Conditional Column | Add a new column with values based on specified conditions or logical expressions. |
| 16 | Fill Down/Up | Populate empty cells in a column with the value from the nearest non-empty cell above or below. |
| 17 | Data Splitting | Split text values in a column into multiple columns based on predefined delimiters or patterns. |
| 18 | Data Type Casting | Convert values in a column to a different data type, such as converting text to numbers or dates. |
| 19 | Custom Transformation | Implement custom transformations using M-code, the underlying language of Power Query. |
These transformations empower users to clean, manipulate, and prepare their data for analysis or reporting, ensuring data accuracy, consistency, and relevance.
Power Query Editor Interface:
The following illustration shows a few of the transformations available in Power Query Editor.
Steps to access Power Query Editor:
Difference between Transform Tab and Add Column
- Transform Tab: This is where you tweak your data. Need to split a column, change data types, or clean up messy data? You’ll find all those tools here. It’s like your data makeover studio!
- Add Column Tab: Think of this as your data expansion kit. Here, you can add new columns based on existing data or bring in fresh insights from other sources. It’s all about giving your dataset a boost with extra information.
Where can we use Power Query?
Power Query is a versatile tool that can be used in various contexts across the Microsoft Power Platform. Let’s explore where you can leverage its magic:
| Product | M Engine | Power Query Desktop | Power Query Online | Dataflows (Power Platform) |
|---|---|---|---|---|
| Excel for Windows | No | No | No | No |
| Power BI Desktop | Yes | Yes | Yes | Yes |
| Power Apps | No | No | No | Yes |
| Power Automate | No | No | No | Yes |
| Power Virtual Agents | No | No | No | Yes |
| Azure Data Factory | No | No | No | Yes |
Power Query Conclusion: The Power Query Editor in Power BI Desktop is a tool for transforming or editing data files before they are loaded into Power BI. The Query Editor acts as an intermediate data container where users can modify data by selecting rows and columns, splitting rows and columns, pivoting and unpivoting columns, and more.
Add comment