Power Query in Excel and Power BI (Ep. 1)

Allison Gonzalez, a Microsoft Certified Trainer at Pragmatic Works, introduces one of her favorite tools for data cleaning: Power Query. This tool, which first appeared in Excel and later in Power BI, offers users an efficient way to clean and manipulate data without requiring complicated coding. While Power Query has evolved over time, its core function remains the same—helping users manage large data sets and simplify data transformation tasks.

What is Power Query?

Power Query is a tool that was initially created to enhance Excel’s data-handling capabilities. As data needs grew, especially with the increasing complexity of data from different sources, Power Query was developed to allow users to connect to various data sources, clean, and transform that data without needing to write complex formulas or scripts.

The tool is available in both Excel and Power BI, where it allows users to interact with data in a straightforward way through an intuitive user interface. The core idea is simple: load the data into Power Query, clean it up, and then load it back into Excel or Power BI for further analysis or reporting.

Key Features and Functions

1. Connecting to Data Sources

Power Query allows users to import data from a wide variety of sources. Whether the data is stored in a local file (Excel, CSV, etc.), a database (SQL Server, Azure), or on the web, Power Query provides an easy way to access and import it in its native format. Once the data is loaded into Power Query, users can start transforming it to meet their specific needs.

2. Intuitive Data Transformation

One of Power Query’s most powerful features is its ability to transform data with ease. Whether you are cleaning up a messy dataset or performing complex calculations, Power Query’s user-friendly interface enables you to accomplish tasks like:

• Removing unnecessary columns
• Changing data types
• Filtering rows
• Combining data from multiple sources

All of this can be done with just a few clicks, without writing any code.

3. Applied Steps Panel

A unique feature in Power Query is the "Applied Steps" panel, where every action you take (such as removing a column or filtering rows) is recorded and saved. If you make a mistake or need to adjust something, you can go back and modify the steps. This makes the transformation process more transparent and easier to troubleshoot.

4. Column from Example

Another time-saving feature in Power Query is the "Column from Example" tool. This feature allows users to create new columns by simply typing in an example of what they want. Power Query automatically recognizes the pattern and generates the column for the entire dataset, making it incredibly efficient for repetitive tasks.

Power Query in Excel vs. Power Query in Power BI

Although Power Query operates similarly in both Excel and Power BI, there are a few visual and structural differences.

• Color Scheme: Excel uses green accents, while Power BI uses yellow accents, making it easy to distinguish between the two programs.
• Interface Layout: In Power BI, the source buttons are located towards the front of the ribbon, whereas in Excel, they appear at the end. This minor difference is just a matter of preference and doesn’t affect the overall functionality of the tool.
• Data Load: When using Power Query in Excel, the cleaned data can be loaded directly into Excel or into PowerPivot. In Power BI, however, the data is loaded into the Power BI data model, allowing for deeper integration with Power BI’s reporting features.

Practical Example: Cleaning Data

In the video, Allison demonstrates a practical use case by importing customer data from an Excel workbook into Power Query. Here’s a breakdown of the steps:

• Import Data: First, Allison imports data from an Excel workbook using the "Get Data" option in the Power Query editor.
• Remove Unnecessary Columns: Next, she removes unnecessary columns that don’t contribute to the analysis. Using Power Query’s intuitive interface, this task becomes much easier than manually deleting each column in Excel.
• Add a New Column: Allison adds a "Full Name" column by combining the first name and last name columns using the "Column from Example" feature. This is a perfect example of how Power Query helps users accomplish tasks quickly without needing to write any formulas.
• Load Data: After making the necessary transformations, the data is loaded back into Excel or Power BI for further analysis.

Advanced Features

• Query Editor: Power Query’s query editor allows users to interact with their data step-by-step, making it easy to track changes. Each transformation you apply is recorded in the "Applied Steps" section, where you can go back and modify any action.
• M Language: For those who are familiar with programming, Power Query uses the M language (also known as mashup language) to perform transformations. This language is automatically generated in the background, but you can access it via the "Advanced Editor" if you need more control over your queries.

Conclusion

Power Query is a versatile and powerful tool for anyone working with large datasets, whether you are in Excel or Power BI. Its user-friendly interface and robust functionality allow users to clean, transform, and analyze data without writing complex code. Whether you are a beginner or an experienced user, Power Query can significantly improve your workflow and save you time. To dive deeper into Power Query, visit Pragmatic Works’ extensive library of training materials and resources.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Power Query and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.