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
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:
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
I hope you enjoyed this article, and the Power Query video. Do write a comment, and let me know what you liked about it.
Founder of ExcelChamp.Net