{"id":1850,"date":"2023-07-17T17:14:47","date_gmt":"2023-07-17T09:14:47","guid":{"rendered":"https:\/\/www.excelchamp.net\/?p=1850"},"modified":"2023-07-17T17:25:59","modified_gmt":"2023-07-17T09:25:59","slug":"power-query-the-hidden-feature-of-excel-to-clean-dirty-data-in-minutes","status":"publish","type":"post","link":"https:\/\/www.excelchamp.net\/power-query-the-hidden-feature-of-excel-to-clean-dirty-data-in-minutes\/","title":{"rendered":"Power Query: The Hidden Feature of Excel To Clean Dirty Data in Minutes"},"content":{"rendered":"<p>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.<\/p>\n<p>It only takes a few clicks to clean this data and then we can analyze it using a Excel Pivot Table.<\/p>\n<p>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.<\/p>\n<h2>How To Clean Dirty Data With Microsoft Power Query within Excel &amp; Power BI<\/h2>\n<p><iframe title=\"Cleaning Multiple Levels of Excel Data With Power Query: Quickly &amp; Easily\" width=\"1200\" height=\"675\" src=\"https:\/\/www.youtube.com\/embed\/PPmHpnaSB0w?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" allowfullscreen><\/iframe><\/p>\n<p><strong>Presented by<\/strong> <a href=\"https:\/\/www.excelchamp.net\">Vinai Prakash, Founder of ExcelChamp.Net<\/a><\/p>\n<p><strong><a href=\"https:\/\/youtube.com\/live\/PPmHpnaSB0w\" target=\"_blank\" rel=\"noopener\">Practice Excel File for Your Benefit<\/a>\u00a0<\/strong><\/p>\n<h2>This video demonstrates Power Query in Microsoft Excel 365, Excel\u00a0 2019, Excel 2021, Excel 2016, Excel 2013, Excel 2010.<\/h2>\n<h3>The key data cleanup tools within Power Query that are demonstrated are:<\/h3>\n<ul>\n<li>\u00a0How to Transpose the data in Power Query<\/li>\n<li>\u00a0How to Change the Name of a Query in Power Query<\/li>\n<li>\u00a0How the steps of the cleanup are recorded within Power Query<\/li>\n<li>\u00a0How to Rename Columns in Power Query<\/li>\n<li>\u00a0How to Promote the First Row as Headers in Power Query<\/li>\n<li>\u00a0How to Fill Down Values from the rows above in Power Query<\/li>\n<li>\u00a0How to Unpivot Other Columns in Power Query<\/li>\n<li>\u00a0How to Save &amp; Load the Cleaned up Data in Power Query<\/li>\n<li>\u00a0How to Create a Pivot Table in Microsoft Excel<\/li>\n<li>\u00a0How to Filter Data in a Pivot Table<\/li>\n<li>\u00a0Limitations of the Filter Section in an Excel Pivot Table<\/li>\n<li>\u00a0How to Add a Slicer in Excel Pivot Tables<\/li>\n<li>\u00a0How to select multiple values in a Slicer, and<\/li>\n<li>\u00a0How the Slicer is superior than a Filter in Excel<\/li>\n<\/ul>\n<p>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.<\/p>\n<p><strong>Do like the video<\/strong>, <strong><a href=\"https:\/\/youtube.com\/excelchamp?sub_confirmation=1\" target=\"_blank\" rel=\"noopener\">subscribe to our channel<\/a><\/strong>, and Give our video a Thumbs Up.<\/p>\n<p>We&#8217;d love a comment about how this video helped you. I reply to all comments personally.\u00a0Thanks!<\/p>\n<h2>What is Power Query?<\/h2>\n<p>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.<\/p>\n<h3>Here are some key features and functionalities of Power Query in Excel:<\/h3>\n<ol>\n<li><strong>Data Import:<\/strong> 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.<\/li>\n<li><strong>Data Transformation:<\/strong> 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.<\/li>\n<li><strong>Data Connection and Refresh:<\/strong> 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.<\/li>\n<li><strong>Data Cleanup and Data Quality:<\/strong> 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.<\/li>\n<li><strong>Query Folding and Performance Optimization:<\/strong> 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.<\/li>\n<li><strong>Data Combining and Merging:<\/strong> 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.<\/li>\n<li><strong>Custom Data Transformations:<\/strong> 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.<\/li>\n<li><strong>Data Visualization and Reporting:<\/strong> Once the data is imported and transformed using Power Query, you can load it into Excel for further analysis or create reports using Excel&#8217;s data visualization tools, such as PivotTables, PivotCharts, and Power View.<\/li>\n<\/ol>\n<p>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.<\/p>\n<div class=\"flex-1 overflow-hidden\">\n<div class=\"react-scroll-to-bottom--css-mdhcx-79elbk h-full dark:bg-gray-800\">\n<div class=\"react-scroll-to-bottom--css-mdhcx-1n7m0yu\">\n<div class=\"flex flex-col text-sm dark:bg-gray-800\">\n<div class=\"group w-full text-gray-800 dark:text-gray-100 border-b border-black\/10 dark:border-gray-900\/50 dark:bg-gray-800\">\n<div class=\"flex p-4 gap-4 text-base md:gap-6 md:max-w-2xl lg:max-w-[38rem] xl:max-w-3xl md:py-6 lg:px-0 m-auto\">\n<div class=\"relative flex w-[calc(100%-50px)] flex-col gap-1 md:gap-3 lg:w-[calc(100%-115px)]\">\n<div class=\"flex flex-grow flex-col gap-3\">\n<div class=\"min-h-[20px] flex items-start overflow-x-auto whitespace-pre-wrap break-words flex-col gap-4\">\n<h2 class=\"empty:hidden\">Who Should Learn Power Query?<\/h2>\n<\/div>\n<\/div>\n<blockquote>\n<div class=\"text-gray-400 flex self-end lg:self-center justify-center mt-2 gap-2 md:gap-3 lg:gap-1 lg:absolute lg:top-0 lg:translate-x-full lg:right-0 lg:mt-0 lg:pl-2 visible\">Power Query is a powerful data transformation and preparation tool in Excel that can benefit a wide range of individuals and professionals.<\/div>\n<\/blockquote>\n<h3 class=\"text-gray-400 flex self-end lg:self-center justify-center mt-2 gap-2 md:gap-3 lg:gap-1 lg:absolute lg:top-0 lg:translate-x-full lg:right-0 lg:mt-0 lg:pl-2 visible\">Here are some key groups of people who should consider learning Power Query:<\/h3>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"group w-full text-gray-800 dark:text-gray-100 border-b border-black\/10 dark:border-gray-900\/50 bg-gray-50 dark:bg-[#444654]\">\n<div class=\"flex p-4 gap-4 text-base md:gap-6 md:max-w-2xl lg:max-w-[38rem] xl:max-w-3xl md:py-6 lg:px-0 m-auto\">\n<div class=\"relative flex w-[calc(100%-50px)] flex-col gap-1 md:gap-3 lg:w-[calc(100%-115px)]\">\n<div class=\"flex flex-grow flex-col gap-3\">\n<div class=\"min-h-[20px] flex items-start overflow-x-auto whitespace-pre-wrap break-words flex-col gap-4\">\n<div class=\"markdown prose w-full break-words dark:prose-invert dark\">\n<ol>\n<li><strong>Data Analysts and Business Analysts:<\/strong> 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.<\/li>\n<li><strong>Excel Power Users:<\/strong> 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&#8217;s built-in functions and features, allowing for more efficient and flexible data preparation.<\/li>\n<li><strong>Financial Professionals:<\/strong> 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.<\/li>\n<li><strong>Data Scientists and Researchers:<\/strong> 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.<\/li>\n<li><strong>IT Professionals and Data Engineers:<\/strong> 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.<\/li>\n<li><strong>Project Managers:<\/strong> 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.<\/li>\n<li><strong>Sales and Marketing Professionals:<\/strong> 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.<\/li>\n<li><strong>Administrators and Operations Professionals:<\/strong> 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.<\/li>\n<li><strong>Educators and Trainers:<\/strong> 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.<\/li>\n<li><strong>Any Excel User Seeking Advanced Data Preparation Skills:<\/strong> 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.<\/li>\n<\/ol>\n<p>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.<\/p>\n<h2>Is Power Query Easy To Learn?<\/h2>\n<blockquote><p>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:<\/p><\/blockquote>\n<ol>\n<li><strong>Visual Interface:<\/strong> 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.<\/li>\n<li><strong>Intuitive Operations:<\/strong> 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.<\/li>\n<li><strong>Query Dependencies and Applied Steps:<\/strong> 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.<\/li>\n<li><strong>Query Editor Preview:<\/strong> 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.<\/li>\n<li><strong>Reusability and Automation:<\/strong> 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.<\/li>\n<\/ol>\n<p>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.<\/p>\n<p>With the availability of classroom trainings, documentation, and community support, users can find ample resources to assist them in learning and <a href=\"https:\/\/www.intellisoft.com.sg\/what-is-power-bi-a-must-have-skill-for-analysts-decision-makers.html\" target=\"_blank\" rel=\"noopener\">mastering Power Query<\/a>.<\/p>\n<p>Intellisoft Training runs a <a href=\"https:\/\/www.intellisoft.com.sg\/power-bi-training-singapore.html\" target=\"_blank\" rel=\"noopener\">Power BI MasterClass, which covers quite a bit of cleanup of data using Power Query functions and features<\/a>.<\/p>\n<p>Feel free to join it, or engage Intellisoft for a <strong><a href=\"https:\/\/www.excelchamp.net\/online-data-analytics-visualization-with-power-bi\/\" target=\"_blank\" rel=\"noopener\">Corporate Training on Data Analytics &amp; Data Visualization With Power BI<\/a><\/strong><\/p>\n<p>Overall, users with basic Excel skills and a willingness to explore and experiment can quickly grasp the concepts and functionalities of <a href=\"https:\/\/www.intellisoft.com.sg\/microsoft-power-bi-super-charge-your-data-analysis-process.html\" target=\"_blank\" rel=\"noopener\">Power Query\u00a0 in Power BI<\/a> and start utilizing it for data preparation and analysis tasks.<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<p>I hope you enjoyed this article, and the <a href=\"https:\/\/youtube.com\/live\/PPmHpnaSB0w\" target=\"_blank\" rel=\"noopener\">Power Query video<\/a>. Do write a comment, and let me know what you liked about it.<\/p>\n<p>Cheers,<br \/>\nVinai Prakash<br \/>\nFounder of ExcelChamp.Net<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 &#8230; <a title=\"Power Query: The Hidden Feature of Excel To Clean Dirty Data in Minutes\" class=\"read-more\" href=\"https:\/\/www.excelchamp.net\/power-query-the-hidden-feature-of-excel-to-clean-dirty-data-in-minutes\/\" aria-label=\"More on Power Query: The Hidden Feature of Excel To Clean Dirty Data in Minutes\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"generate_page_header":"","footnotes":"","_jetpack_memberships_contains_paid_content":false},"categories":[6,8,104],"tags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.6 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Power Query: The Hidden Feature of Excel To Clean Dirty Data in Minutes - ExcelChamp<\/title>\n<meta name=\"description\" content=\"Step by Step Tutorial on How to Use Excel Power Query Tool to Cleanup Dirty Data in just a few steps, and analyze it with Pivot &amp; Slicers.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.excelchamp.net\/power-query-the-hidden-feature-of-excel-to-clean-dirty-data-in-minutes\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Power Query: The Hidden Feature of Excel To Clean Dirty Data in Minutes - ExcelChamp\" \/>\n<meta property=\"og:description\" content=\"Step by Step Tutorial on How to Use Excel Power Query Tool to Cleanup Dirty Data in just a few steps, and analyze it with Pivot &amp; Slicers.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.excelchamp.net\/power-query-the-hidden-feature-of-excel-to-clean-dirty-data-in-minutes\/\" \/>\n<meta property=\"og:site_name\" content=\"ExcelChamp\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/profile.php?id=100079767634794\" \/>\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/profile.php?id=100079767634794\" \/>\n<meta property=\"article:published_time\" content=\"2023-07-17T09:14:47+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-07-17T09:25:59+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/i0.wp.com\/www.excelchamp.net\/wp-content\/uploads\/2022\/06\/VinaiPrakash-e1654524348231.png?fit=331%2C314&ssl=1\" \/>\n\t<meta property=\"og:image:width\" content=\"331\" \/>\n\t<meta property=\"og:image:height\" content=\"314\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Vinai Prakash\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vinai Prakash\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.excelchamp.net\/power-query-the-hidden-feature-of-excel-to-clean-dirty-data-in-minutes\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.excelchamp.net\/power-query-the-hidden-feature-of-excel-to-clean-dirty-data-in-minutes\/\"},\"author\":{\"name\":\"Vinai Prakash\",\"@id\":\"https:\/\/www.excelchamp.net\/#\/schema\/person\/e82f27afcc1c8f354753c53f433284ba\"},\"headline\":\"Power Query: The Hidden Feature of Excel To Clean Dirty Data in Minutes\",\"datePublished\":\"2023-07-17T09:14:47+00:00\",\"dateModified\":\"2023-07-17T09:25:59+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.excelchamp.net\/power-query-the-hidden-feature-of-excel-to-clean-dirty-data-in-minutes\/\"},\"wordCount\":1769,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.excelchamp.net\/#\/schema\/person\/e82f27afcc1c8f354753c53f433284ba\"},\"articleSection\":[\"Excel In-Built Functionality\",\"Excel Video Tutorials\",\"Power BI\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.excelchamp.net\/power-query-the-hidden-feature-of-excel-to-clean-dirty-data-in-minutes\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.excelchamp.net\/power-query-the-hidden-feature-of-excel-to-clean-dirty-data-in-minutes\/\",\"url\":\"https:\/\/www.excelchamp.net\/power-query-the-hidden-feature-of-excel-to-clean-dirty-data-in-minutes\/\",\"name\":\"Power Query: The Hidden Feature of Excel To Clean Dirty Data in Minutes - ExcelChamp\",\"isPartOf\":{\"@id\":\"https:\/\/www.excelchamp.net\/#website\"},\"datePublished\":\"2023-07-17T09:14:47+00:00\",\"dateModified\":\"2023-07-17T09:25:59+00:00\",\"description\":\"Step by Step Tutorial on How to Use Excel Power Query Tool to Cleanup Dirty Data in just a few steps, and analyze it with Pivot & Slicers.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.excelchamp.net\/power-query-the-hidden-feature-of-excel-to-clean-dirty-data-in-minutes\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.excelchamp.net\/power-query-the-hidden-feature-of-excel-to-clean-dirty-data-in-minutes\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.excelchamp.net\/power-query-the-hidden-feature-of-excel-to-clean-dirty-data-in-minutes\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.excelchamp.net\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Power Query: The Hidden Feature of Excel To Clean Dirty Data in Minutes\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.excelchamp.net\/#website\",\"url\":\"https:\/\/www.excelchamp.net\/\",\"name\":\"ExcelChamp\",\"description\":\"Solving Everyday Problems With Excel\",\"publisher\":{\"@id\":\"https:\/\/www.excelchamp.net\/#\/schema\/person\/e82f27afcc1c8f354753c53f433284ba\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.excelchamp.net\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/www.excelchamp.net\/#\/schema\/person\/e82f27afcc1c8f354753c53f433284ba\",\"name\":\"Vinai Prakash\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.excelchamp.net\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/i0.wp.com\/www.excelchamp.net\/wp-content\/uploads\/2022\/06\/VinaiPrakash.png?fit=487%2C720&ssl=1\",\"contentUrl\":\"https:\/\/i0.wp.com\/www.excelchamp.net\/wp-content\/uploads\/2022\/06\/VinaiPrakash.png?fit=487%2C720&ssl=1\",\"width\":487,\"height\":720,\"caption\":\"Vinai Prakash\"},\"logo\":{\"@id\":\"https:\/\/www.excelchamp.net\/#\/schema\/person\/image\/\"},\"description\":\"Vinai Prakash is the founder and master trainer of ExcelChamp.Net. Vinai has over 30 years of experience in using Excel, and is a Data Warehousing &amp; Business Intelligence Professional. Vinai has coached and trained over 30,000 professionals in using Excel, Power BI, Python, SQL, and regularly runs Excel Data Analysis, Excel Dashboard MasterClass, Excel for Beginners, Advanced Excel MasterClass online and all over the world. Vinai's Videos on YouTube channel and his blog articles and newsletters are read by thousands of subscribers all over the world. Vinai lives in Singapore with his wife and 2 kids.\",\"sameAs\":[\"https:\/\/ExcelChamp.Net\",\"https:\/\/www.facebook.com\/profile.php?id=100079767634794\",\"https:\/\/www.linkedin.com\/in\/vinaiprakash\/\",\"https:\/\/www.youtube.com\/user\/ExcelChamp\"],\"url\":\"https:\/\/www.excelchamp.net\/author\/ppfdd\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Power Query: The Hidden Feature of Excel To Clean Dirty Data in Minutes - ExcelChamp","description":"Step by Step Tutorial on How to Use Excel Power Query Tool to Cleanup Dirty Data in just a few steps, and analyze it with Pivot & Slicers.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.excelchamp.net\/power-query-the-hidden-feature-of-excel-to-clean-dirty-data-in-minutes\/","og_locale":"en_US","og_type":"article","og_title":"Power Query: The Hidden Feature of Excel To Clean Dirty Data in Minutes - ExcelChamp","og_description":"Step by Step Tutorial on How to Use Excel Power Query Tool to Cleanup Dirty Data in just a few steps, and analyze it with Pivot & Slicers.","og_url":"https:\/\/www.excelchamp.net\/power-query-the-hidden-feature-of-excel-to-clean-dirty-data-in-minutes\/","og_site_name":"ExcelChamp","article_publisher":"https:\/\/www.facebook.com\/profile.php?id=100079767634794","article_author":"https:\/\/www.facebook.com\/profile.php?id=100079767634794","article_published_time":"2023-07-17T09:14:47+00:00","article_modified_time":"2023-07-17T09:25:59+00:00","og_image":[{"width":331,"height":314,"url":"https:\/\/i0.wp.com\/www.excelchamp.net\/wp-content\/uploads\/2022\/06\/VinaiPrakash-e1654524348231.png?fit=331%2C314&ssl=1","type":"image\/png"}],"author":"Vinai Prakash","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Vinai Prakash","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.excelchamp.net\/power-query-the-hidden-feature-of-excel-to-clean-dirty-data-in-minutes\/#article","isPartOf":{"@id":"https:\/\/www.excelchamp.net\/power-query-the-hidden-feature-of-excel-to-clean-dirty-data-in-minutes\/"},"author":{"name":"Vinai Prakash","@id":"https:\/\/www.excelchamp.net\/#\/schema\/person\/e82f27afcc1c8f354753c53f433284ba"},"headline":"Power Query: The Hidden Feature of Excel To Clean Dirty Data in Minutes","datePublished":"2023-07-17T09:14:47+00:00","dateModified":"2023-07-17T09:25:59+00:00","mainEntityOfPage":{"@id":"https:\/\/www.excelchamp.net\/power-query-the-hidden-feature-of-excel-to-clean-dirty-data-in-minutes\/"},"wordCount":1769,"commentCount":0,"publisher":{"@id":"https:\/\/www.excelchamp.net\/#\/schema\/person\/e82f27afcc1c8f354753c53f433284ba"},"articleSection":["Excel In-Built Functionality","Excel Video Tutorials","Power BI"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.excelchamp.net\/power-query-the-hidden-feature-of-excel-to-clean-dirty-data-in-minutes\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.excelchamp.net\/power-query-the-hidden-feature-of-excel-to-clean-dirty-data-in-minutes\/","url":"https:\/\/www.excelchamp.net\/power-query-the-hidden-feature-of-excel-to-clean-dirty-data-in-minutes\/","name":"Power Query: The Hidden Feature of Excel To Clean Dirty Data in Minutes - ExcelChamp","isPartOf":{"@id":"https:\/\/www.excelchamp.net\/#website"},"datePublished":"2023-07-17T09:14:47+00:00","dateModified":"2023-07-17T09:25:59+00:00","description":"Step by Step Tutorial on How to Use Excel Power Query Tool to Cleanup Dirty Data in just a few steps, and analyze it with Pivot & Slicers.","breadcrumb":{"@id":"https:\/\/www.excelchamp.net\/power-query-the-hidden-feature-of-excel-to-clean-dirty-data-in-minutes\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.excelchamp.net\/power-query-the-hidden-feature-of-excel-to-clean-dirty-data-in-minutes\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.excelchamp.net\/power-query-the-hidden-feature-of-excel-to-clean-dirty-data-in-minutes\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.excelchamp.net\/"},{"@type":"ListItem","position":2,"name":"Power Query: The Hidden Feature of Excel To Clean Dirty Data in Minutes"}]},{"@type":"WebSite","@id":"https:\/\/www.excelchamp.net\/#website","url":"https:\/\/www.excelchamp.net\/","name":"ExcelChamp","description":"Solving Everyday Problems With Excel","publisher":{"@id":"https:\/\/www.excelchamp.net\/#\/schema\/person\/e82f27afcc1c8f354753c53f433284ba"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.excelchamp.net\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/www.excelchamp.net\/#\/schema\/person\/e82f27afcc1c8f354753c53f433284ba","name":"Vinai Prakash","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.excelchamp.net\/#\/schema\/person\/image\/","url":"https:\/\/i0.wp.com\/www.excelchamp.net\/wp-content\/uploads\/2022\/06\/VinaiPrakash.png?fit=487%2C720&ssl=1","contentUrl":"https:\/\/i0.wp.com\/www.excelchamp.net\/wp-content\/uploads\/2022\/06\/VinaiPrakash.png?fit=487%2C720&ssl=1","width":487,"height":720,"caption":"Vinai Prakash"},"logo":{"@id":"https:\/\/www.excelchamp.net\/#\/schema\/person\/image\/"},"description":"Vinai Prakash is the founder and master trainer of ExcelChamp.Net. Vinai has over 30 years of experience in using Excel, and is a Data Warehousing &amp; Business Intelligence Professional. Vinai has coached and trained over 30,000 professionals in using Excel, Power BI, Python, SQL, and regularly runs Excel Data Analysis, Excel Dashboard MasterClass, Excel for Beginners, Advanced Excel MasterClass online and all over the world. Vinai's Videos on YouTube channel and his blog articles and newsletters are read by thousands of subscribers all over the world. Vinai lives in Singapore with his wife and 2 kids.","sameAs":["https:\/\/ExcelChamp.Net","https:\/\/www.facebook.com\/profile.php?id=100079767634794","https:\/\/www.linkedin.com\/in\/vinaiprakash\/","https:\/\/www.youtube.com\/user\/ExcelChamp"],"url":"https:\/\/www.excelchamp.net\/author\/ppfdd\/"}]}},"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/www.excelchamp.net\/wp-json\/wp\/v2\/posts\/1850"}],"collection":[{"href":"https:\/\/www.excelchamp.net\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.excelchamp.net\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.excelchamp.net\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.excelchamp.net\/wp-json\/wp\/v2\/comments?post=1850"}],"version-history":[{"count":4,"href":"https:\/\/www.excelchamp.net\/wp-json\/wp\/v2\/posts\/1850\/revisions"}],"predecessor-version":[{"id":1854,"href":"https:\/\/www.excelchamp.net\/wp-json\/wp\/v2\/posts\/1850\/revisions\/1854"}],"wp:attachment":[{"href":"https:\/\/www.excelchamp.net\/wp-json\/wp\/v2\/media?parent=1850"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.excelchamp.net\/wp-json\/wp\/v2\/categories?post=1850"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.excelchamp.net\/wp-json\/wp\/v2\/tags?post=1850"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}