{"id":31511,"date":"2023-10-13T21:59:26","date_gmt":"2023-10-13T19:59:26","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=31511"},"modified":"2024-03-22T18:09:27","modified_gmt":"2024-03-22T17:09:27","slug":"power-query-in-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/power-query-in-excel\/","title":{"rendered":"How to Clean and Transform Data Using Power Query in Excel"},"content":{"rendered":"<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/42CB0A0B-9872-4C33-A2B9-7CD396CC9A02.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-31565\" title=\"How to Clean and Transform Data Using Power Query in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/42CB0A0B-9872-4C33-A2B9-7CD396CC9A02-1024x576.png\" alt=\"How to Clean and Transform Data Using Power Query in Excel\" width=\"1024\" height=\"576\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/42CB0A0B-9872-4C33-A2B9-7CD396CC9A02-1024x576.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/42CB0A0B-9872-4C33-A2B9-7CD396CC9A02-300x169.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/42CB0A0B-9872-4C33-A2B9-7CD396CC9A02-768x432.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/42CB0A0B-9872-4C33-A2B9-7CD396CC9A02.png 1280w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/p>\n<p>Power Query in Excel is an <strong>efficient data analysis tool <\/strong>that can be used to <strong>manipulate and transform data<\/strong>. It can be used to <strong>simplify and automate common tasks<\/strong> in Excel. It <strong>eliminates the need to learn complex code<\/strong> as tasks can be performed with just a simple click.<\/p>\n<p>In this article, we will cover the following topics in detail &#8211;<\/p>\n<p>Download the Excel Workbook below to follow along and understand How to Use Power Query in Excel \u2013<br \/>\n<a class=\"IRPP_minimalist icon-download\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel.xlsx\" target=\"_blank\"><span class=\"ctaText px-3 text-capitalize\"><strong>download excel workbook<\/strong><\/span><span class=\"postTitle px-3\">Power-Query-in-Excel.xlsx<\/span><\/a><\/p>\n<p>&nbsp;<\/p>\n<h3><strong><span id=\"1\" style=\"color: #ff0000;\">Introduction to Power Query in Excel<\/span><\/strong><\/h3>\n<p>It<strong> simplifies the process of importing, transforming, and cleaning data<\/strong>, making it an invaluable tool for data analysis or reporting. You can<strong> import data from different data sources, transform it as per your requirements, and then load it back<\/strong> to the data worksheet.<\/p>\n<p>Power Query<strong> accesses, cleans, and transforms all that messy data<\/strong> and displays it <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/transpose-in-excel\/\" target=\"_blank\" rel=\"noopener\">in a way that Excel<\/a> loves and can work with. So<strong> no more formulas<\/strong>,<strong> <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/split-first-last-name-using-excel-text-column\/\" target=\"_blank\" rel=\"nofollow noopener\">text to columns<\/a>, <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/cleaning-data-with-the-trim-formula-excel\/\" target=\"_blank\" rel=\"nofollow noopener\">trim spaces<\/a>, <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/vlookup-function-introduction-excel\/\" target=\"_blank\" rel=\"nofollow noopener\">VLOOKUP<\/a>, <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/find-and-replace\/\" target=\"_blank\" rel=\"nofollow noopener\">find &amp; replace<\/a><\/strong>, etc.<\/p>\n<p>Once the<strong> query has been set<\/strong>, you can simply<strong> refresh it every time<\/strong> you want to run the steps again. So when you need to<strong> create the same report with updated data, a simple REFRESH <\/strong>will transform the report. It helps you <strong>save hours of time and reduce the risk of manual errors<\/strong>.<\/p>\n<p>Power Query empowers users to focus on deriving meaningful insights and making data-driven decisions.<\/p>\n<p>&nbsp;<\/p>\n<h3><strong><span id=\"2\" style=\"color: #ff0000;\">Enable Power Query<\/span><\/strong><\/h3>\n<p>Power Query is <strong>available as a free add-in for Excel 2010 and 2013<\/strong> users. Once you install it, you will see the Power Query tab in the Excel ribbon.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-1.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-31514\" title=\"How to Clean and Transform Data Using Power Query in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-1.png\" alt=\"How to Clean and Transform Data Using Power Query in Excel\" width=\"611\" height=\"123\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-1.png 611w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-1-300x60.png 300w\" sizes=\"(max-width: 611px) 100vw, 611px\" \/><\/a><\/p>\n<p><strong><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/install-power-query-with-excel-2013\/\" target=\"_blank\" rel=\"nofollow noopener\">Click to see a tutorial on how to install Power Query in Excel 2013<\/a><\/strong><\/p>\n<p><strong><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/installing-power-query-excel-2010\/\" target=\"_blank\" rel=\"nofollow noopener\">Click to see a tutorial on how to install Power Query in Excel 2010<\/a><\/strong><\/p>\n<p>For <strong>Excel 2016<\/strong> and later versions, Power Query is<strong> built into the application<\/strong>.<\/p>\n<ul>\n<li>Open the Excel application, and click on the <strong>Data<\/strong> tab in the Ribbon.<br \/>\n<a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-2.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-31515\" title=\"How to Clean and Transform Data Using Power Query in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-2.png\" alt=\"How to Clean and Transform Data Using Power Query in Excel\" width=\"1920\" height=\"167\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-2.png 1920w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-2-300x26.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-2-1024x89.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-2-768x67.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-2-1536x134.png 1536w\" sizes=\"(max-width: 1920px) 100vw, 1920px\" \/><\/a><\/li>\n<li>Look for the<strong> Get &amp; Transform<\/strong> group. This group contains all Power Query tools.<br \/>\n<a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-31519\" title=\"How to Clean and Transform Data Using Power Query in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel.png\" alt=\"How to Clean and Transform Data Using Power Query in Excel\" width=\"1920\" height=\"167\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel.png 1920w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-300x26.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-1024x89.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-768x67.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-1536x134.png 1536w\" sizes=\"(max-width: 1920px) 100vw, 1920px\" \/><\/a><\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<h3><strong><span id=\"3\" style=\"color: #ff0000;\">Power Query Workflow<\/span><\/strong><\/h3>\n<p>When working with Power Query in Excel, the entire process is divided into 4 main steps &#8211;<\/p>\n<p><strong><span style=\"color: #ff0000;\"> STEP 1 &#8211; Import Data<\/span><\/strong><\/p>\n<p>You can<strong> import data from multiple sources<\/strong> like Excel, CSV, web, pdf files, etc. This step<strong> eliminates the need for manual <a class=\"wpil_keyword_link\" title=\"data entry\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/create-data-entry-form-excel\/\" target=\"_blank\" rel=\"noopener\" data-wpil-keyword-link=\"linked\">data entry<\/a><\/strong> and ensures a <strong>seamless integration<\/strong> of external data.<\/p>\n<p>To import data, go to <strong>Data &gt; Get Data<\/strong> and select the required option.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-3.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-31516\" title=\"How to Clean and Transform Data Using Power Query in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-3.png\" alt=\"How to Clean and Transform Data Using Power Query in Excel\" width=\"1215\" height=\"597\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-3.png 1215w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-3-300x147.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-3-1024x503.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-3-768x377.png 768w\" sizes=\"(max-width: 1215px) 100vw, 1215px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 2 &#8211; Transform Data<\/span><\/strong><\/p>\n<p>Once you have the data in the Power Query window, you can easily use the<strong> vast range of built-in data cleaning and transformation tools like removing duplicates, changing cases, transposing, unpivoting data, splitting text, filtering records<\/strong>, and much more.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/50-things-you-can-do-with-excel-power-query\/\" target=\"_blank\" rel=\"nofollow noopener\"><strong>Click here<\/strong><\/a> to learn 50 different things you can do with Excel Power Query.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-4-1.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-31520\" title=\"How to Clean and Transform Data Using Power Query in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-4-1.png\" alt=\"How to Clean and Transform Data Using Power Query in Excel\" width=\"1920\" height=\"974\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-4-1.png 1920w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-4-1-300x152.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-4-1-1024x519.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-4-1-768x390.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-4-1-1536x779.png 1536w\" sizes=\"(max-width: 1920px) 100vw, 1920px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 3 &#8211; Load Data<\/span><\/strong><\/p>\n<p>Once the data has been imported and transformed, the final step involves loading it back into Excel to create reports. To load the data, simply click on the<strong> Home tab &gt; Close &amp; Load<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-5.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-31518\" title=\"How to Clean and Transform Data Using Power Query in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-5.png\" alt=\"How to Clean and Transform Data Using Power Query in Excel\" width=\"1357\" height=\"194\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-5.png 1357w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-5-300x43.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-5-1024x146.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-5-768x110.png 768w\" sizes=\"(max-width: 1357px) 100vw, 1357px\" \/><\/a><\/p>\n<p>Let us look at an example to understand better.<\/p>\n<p>&nbsp;<\/p>\n<h3><strong><span id=\"4\" style=\"color: #ff0000;\">Example of Power Query<\/span><\/strong><\/h3>\n<p>Suppose you have the<strong> product names in one column and their 3 months&#8217; sales in the next 3 months<\/strong> as shown below.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-15.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-31523\" title=\"How to Clean and Transform Data Using Power Query in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-15.png\" alt=\"How to Clean and Transform Data Using Power Query in Excel\" width=\"712\" height=\"509\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-15.png 712w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-15-300x214.png 300w\" sizes=\"(max-width: 712px) 100vw, 712px\" \/><\/a><\/p>\n<p>Now, you want to <strong>change this crosstab format to a tabular one<\/strong>, where you have the product name in 1st column, month in 2nd column, and sales in 3rd.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-16.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-31524\" title=\"How to Clean and Transform Data Using Power Query in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-16.png\" alt=\"How to Clean and Transform Data Using Power Query in Excel\" width=\"517\" height=\"651\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-16.png 517w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-16-238x300.png 238w\" sizes=\"(max-width: 517px) 100vw, 517px\" \/><\/a><\/p>\n<p>This is called unpivoting data where you <strong>convert columns into rows<\/strong>. Each row now represents a <strong>unique combination of a product, a month, and the corresponding sales figure<\/strong>. It is particularly useful when you need to analyze or visualize data to create <a class=\"wpil_keyword_link\" title=\"pivot tables\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/101-excel-pivot-tables\/\" target=\"_blank\" rel=\"noopener\" data-wpil-keyword-link=\"linked\">pivot tables<\/a>.<\/p>\n<p>Changing this format manually can be time-consuming and can lead to errors as well. So, the best approach for this is to use the unpivot option available in the power query. Follow the steps below to achieve the result &#8211;<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 1:<\/span> <\/strong>Go to <strong>Data &gt; From Table\/Range<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-7.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-31525\" title=\"How to Clean and Transform Data Using Power Query in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-7.png\" alt=\"How to Clean and Transform Data Using Power Query in Excel\" width=\"1710\" height=\"161\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-7.png 1710w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-7-300x28.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-7-1024x96.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-7-768x72.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-7-1536x145.png 1536w\" sizes=\"(max-width: 1710px) 100vw, 1710px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 2:<\/span><\/strong> <strong>Select the range<\/strong> and press OK.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-8.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-31526\" title=\"How to Clean and Transform Data Using Power Query in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-8.png\" alt=\"How to Clean and Transform Data Using Power Query in Excel\" width=\"714\" height=\"513\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-8.png 714w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-8-300x216.png 300w\" sizes=\"(max-width: 714px) 100vw, 714px\" \/><\/a><\/p>\n<p>The table will be loaded in the Power query window.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-9.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-31527\" title=\"How to Clean and Transform Data Using Power Query in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-9.png\" alt=\"How to Clean and Transform Data Using Power Query in Excel\" width=\"1584\" height=\"707\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-9.png 1584w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-9-300x134.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-9-1024x457.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-9-768x343.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-9-1536x686.png 1536w\" sizes=\"(max-width: 1584px) 100vw, 1584px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 3:<\/span> <\/strong>Highlight the sales columns, right-click, and select <strong>Change Type &gt; Currency<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-10.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-31528\" title=\"How to Clean and Transform Data Using Power Query in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-10.png\" alt=\"How to Clean and Transform Data Using Power Query in Excel\" width=\"1330\" height=\"713\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-10.png 1330w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-10-300x161.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-10-1024x549.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-10-768x412.png 768w\" sizes=\"(max-width: 1330px) 100vw, 1330px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 4:<\/span><\/strong> Highlight the sales column and go to <strong>Transform &gt; Unpivot Columns<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-11.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-31529\" title=\"How to Clean and Transform Data Using Power Query in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-11.png\" alt=\"How to Clean and Transform Data Using Power Query in Excel\" width=\"1582\" height=\"727\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-11.png 1582w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-11-300x138.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-11-1024x471.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-11-768x353.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-11-1536x706.png 1536w\" sizes=\"(max-width: 1582px) 100vw, 1582px\" \/><\/a><\/p>\n<p>The format of the table will be changed.<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 5:<\/span><\/strong> Simply go to <strong>Home &gt; Close &amp; Load<\/strong> to get the data back to your Excel Worksheet.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-17.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-31533\" title=\"How to Clean and Transform Data Using Power Query in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-17.png\" alt=\"How to Clean and Transform Data Using Power Query in Excel\" width=\"1911\" height=\"914\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-17.png 1911w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-17-300x143.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-17-1024x490.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-17-768x367.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-17-1536x735.png 1536w\" sizes=\"(max-width: 1911px) 100vw, 1911px\" \/><\/a><\/p>\n<p>This is how the data will look in Excel.<\/p>\n<h3><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-14.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-31532\" title=\"How to Clean and Transform Data Using Power Query in Excel\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-14.png\" alt=\"How to Clean and Transform Data Using Power Query in Excel\" width=\"1891\" height=\"899\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-14.png 1891w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-14-300x143.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-14-1024x487.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-14-768x365.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/10\/Power-Query-in-Excel-14-1536x730.png 1536w\" sizes=\"(max-width: 1891px) 100vw, 1891px\" \/><\/a><\/h3>\n<p>Power Query stands as a powerful asset in Excel, streamlining data analysis by simplifying the import, transformation, and cleaning processes. In this article, we have covered topics like enabling Power Query, understanding the workflow, and providing a practical example to showcase its transformative capabilities.<\/p>\n<p><a href=\"https:\/\/learn.microsoft.com\/en-us\/power-query\/power-query-what-is-power-query\" target=\"_blank\" rel=\"nofollow noopener\"><strong>Click here<\/strong><\/a> to learn all about Power Query in Excel!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Power Query in Excel is an efficient data analysis tool that can be used to manipulate and transform data. It can be used to simplify and automate common tasks in Excel. It eliminates the need to learn complex code as tasks can be performed with just a simple click. In this article, we will cover [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":31565,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"How to Clean and Transform Data Using Power Query in Excel","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[121,120],"tags":[1406,117,1405,1407],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/31511"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=31511"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/31511\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/31565"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=31511"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=31511"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=31511"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}