Power Query: The Hidden Feature of Excel To Clean Dirty Data in Minutes

Learn how Power Query, a tool embedded in your current version of Excel can help you to clean dirty data that is made up of multiple levels, but without any headings or format.

It only takes a few clicks to clean this data and then we can analyze it using a Excel Pivot Table.

A Quick and simple step by step tutorial on using Power Query in Excel and then Pivot Tables to Analyze the data, and Slice it using Slicers.

How To Clean Dirty Data With Microsoft Power Query within Excel & Power BI

Presented by Vinai Prakash, Founder of ExcelChamp.Net

Practice Excel File for Your Benefit 

This video demonstrates Power Query in Microsoft Excel 365, Excel  2019, Excel 2021, Excel 2016, Excel 2013, Excel 2010.

The key data cleanup tools within Power Query that are demonstrated are:

  •  How to Transpose the data in Power Query
  •  How to Change the Name of a Query in Power Query
  •  How the steps of the cleanup are recorded within Power Query
  •  How to Rename Columns in Power Query
  •  How to Promote the First Row as Headers in Power Query
  •  How to Fill Down Values from the rows above in Power Query
  •  How to Unpivot Other Columns in Power Query
  •  How to Save & Load the Cleaned up Data in Power Query
  •  How to Create a Pivot Table in Microsoft Excel
  •  How to Filter Data in a Pivot Table
  •  Limitations of the Filter Section in an Excel Pivot Table
  •  How to Add a Slicer in Excel Pivot Tables
  •  How to select multiple values in a Slicer, and
  •  How the Slicer is superior than a Filter in Excel

The Power Query features shown in this video work exactly the same way in Microsoft Power BI also. So you can use this video steps for Power Query within Power BI.

Do like the video, subscribe to our channel, and Give our video a Thumbs Up.

We’d love a comment about how this video helped you. I reply to all comments personally. Thanks!

What is Power Query?

Power Query is a data transformation and data preparation tool available in Microsoft Excel. It is designed to help users import, transform, and combine data from various sources for analysis and reporting purposes. Power Query provides a user-friendly interface and a wide range of data manipulation capabilities, making it easier to clean, reshape, and combine data from multiple sources.

Here are some key features and functionalities of Power Query in Excel:

  1. Data Import: Power Query allows you to import data from various sources, including databases, Excel files, text files, CSV files, web pages, SharePoint lists, and more. It provides a seamless way to connect to and retrieve data from external sources.
  2. Data Transformation: Power Query provides a set of transformation options to clean and reshape data. You can perform tasks such as removing duplicates, filtering rows, splitting columns, merging tables, changing data types, applying calculations, and performing advanced transformations using a visual interface or custom functions.
  3. Data Connection and Refresh: Power Query enables you to create connections to data sources and set up automatic data refresh. This ensures that your Excel workbook stays up-to-date with the latest data from the connected sources, saving you time and effort in manual data updates.
  4. Data Cleanup and Data Quality: Power Query offers various data cleansing and data quality features. You can perform operations like removing empty rows, handling null values, replacing values, standardizing data formats, and detecting and correcting data inconsistencies.
  5. Query Folding and Performance Optimization: Power Query optimizes query performance by utilizing query folding whenever possible. Query folding pushes data transformations to the underlying data source, resulting in faster and more efficient data retrieval and processing.
  6. Data Combining and Merging: With Power Query, you can easily combine and merge data from multiple sources. It allows you to merge tables based on common columns, append data from multiple tables, and perform complex join operations to create a unified dataset.
  7. Custom Data Transformations: Power Query provides advanced capabilities to create custom data transformations using the M formula language. You can write custom functions, perform complex calculations, and create reusable query components to streamline your data transformation workflows.
  8. Data Visualization and Reporting: Once the data is imported and transformed using Power Query, you can load it into Excel for further analysis or create reports using Excel’s data visualization tools, such as PivotTables, PivotCharts, and Power View.

Power Query is available as a built-in feature in Excel 2016 and later versions. It offers a powerful and intuitive way to clean, shape, and combine data from various sources, empowering users to perform advanced data analysis and reporting tasks with ease.

Who Should Learn Power Query?

Power Query is a powerful data transformation and preparation tool in Excel that can benefit a wide range of individuals and professionals.

Here are some key groups of people who should consider learning Power Query:

  1. Data Analysts and Business Analysts: Data analysts and business analysts who work extensively with data can greatly benefit from learning Power Query. It enables them to import and transform data from multiple sources, clean and reshape data, and create consolidated datasets for analysis and reporting purposes.
  2. Excel Power Users: Individuals who are already proficient in Excel and frequently work with data can enhance their skills by learning Power Query. It provides advanced data manipulation capabilities beyond Excel’s built-in functions and features, allowing for more efficient and flexible data preparation.
  3. Financial Professionals: Professionals in finance and accounting roles can leverage Power Query to streamline data analysis and reporting tasks. It enables them to import and combine financial data from various sources, perform data cleansing and transformations, and generate accurate and customized financial reports.
  4. Data Scientists and Researchers: Data scientists, researchers, and individuals working in data-intensive fields can use Power Query to preprocess and prepare data for analysis. It enables them to import and combine large datasets, handle data cleaning and transformation tasks, and create structured datasets ready for advanced analysis.
  5. IT Professionals and Data Engineers: IT professionals and data engineers who are involved in data integration and management can benefit from Power Query. It allows them to connect to various data sources, transform and cleanse data, and prepare it for further processing or loading into data warehouses or data lakes.
  6. Project Managers: Project managers who work with data and need to consolidate and analyze project-related information can use Power Query to streamline their data preparation workflows. It enables them to combine data from different project sources, clean and reshape project data, and create consolidated reports or dashboards.
  7. Sales and Marketing Professionals: Sales and marketing professionals can utilize Power Query to extract and transform data from CRM systems, web analytics tools, and other marketing platforms. It enables them to analyze and consolidate sales and marketing data, create custom reports, and derive insights to optimize sales and marketing strategies.
  8. Administrators and Operations Professionals: Professionals responsible for managing administrative tasks, operations, and data-driven processes can benefit from learning Power Query. It allows them to import and transform data from various systems, automate data cleaning and preparation, and streamline operational workflows.
  9. Educators and Trainers: Educators and trainers who teach data analysis or Excel-related topics can enhance their curriculum by including Power Query. Teaching Power Query equips students with valuable skills in data preparation and manipulation, enhancing their data analysis capabilities.
  10. Any Excel User Seeking Advanced Data Preparation Skills: Even individuals who use Excel for personal or basic business purposes can benefit from learning Power Query. It provides them with advanced data manipulation capabilities, enabling them to clean, transform, and combine data effectively.

Learning Power Query can benefit individuals in various roles and industries, empowering them to efficiently handle data preparation tasks and derive valuable insights from their data.

Is Power Query Easy To Learn?

Power Query is generally considered to be user-friendly and relatively easy to learn, especially for individuals who are already familiar with Excel. Here are a few factors that contribute to the ease of learning Power Query:

  1. Visual Interface: Power Query provides a visual interface within Excel, allowing users to perform data transformations and manipulations through a series of intuitive steps. The interface provides a clear representation of the applied transformations, making it easier to understand and modify the data preparation process.
  2. Intuitive Operations: Power Query offers a wide range of built-in transformations and operations that can be applied to data. These include filtering, sorting, splitting columns, merging tables, pivoting, and much more. The operations are designed to be user-friendly and logical, making it easier for users to select and apply the required transformations.
  3. Query Dependencies and Applied Steps: Power Query keeps track of the applied transformations and presents them as a series of applied steps. Each step is displayed in a structured manner, allowing users to review, modify, or remove any step in the data preparation process. The ability to view and edit applied steps provides transparency and makes it easier to troubleshoot and adjust the transformations.
  4. Query Editor Preview: Power Query provides a preview feature that allows users to see the results of applied transformations in real-time. This live preview helps users understand the impact of each step and make adjustments as needed, ensuring the desired data output.
  5. Reusability and Automation: Power Query supports the creation of reusable queries, which can be applied to multiple datasets. Users can define a set of transformations once and then reuse them on different data sources, saving time and effort. Power Query also allows for automation by enabling users to set up scheduled data refreshes or create connections to external data sources.

While Power Query is generally considered user-friendly, the complexity of data transformation tasks can vary. Advanced data transformations or complex data structures may require more in-depth understanding and practice.

With the availability of classroom trainings, documentation, and community support, users can find ample resources to assist them in learning and mastering Power Query.

Intellisoft Training runs a Power BI MasterClass, which covers quite a bit of cleanup of data using Power Query functions and features.

Feel free to join it, or engage Intellisoft for a Corporate Training on Data Analytics & Data Visualization With Power BI

Overall, users with basic Excel skills and a willingness to explore and experiment can quickly grasp the concepts and functionalities of Power Query  in Power BI and start utilizing it for data preparation and analysis tasks.

I hope you enjoyed this article, and the Power Query video. Do write a comment, and let me know what you liked about it.

Vinai Prakash
Founder of ExcelChamp.Net


Leave a Comment