{"id":280,"date":"2024-11-30T07:02:32","date_gmt":"2024-11-30T07:02:32","guid":{"rendered":"https:\/\/mailitics.com\/index.php\/2024\/11\/30\/think-you-know-excel-take-your-analytics-skills-to-the-next-level-with-power-query-930e2267006e\/"},"modified":"2024-11-30T07:02:32","modified_gmt":"2024-11-30T07:02:32","slug":"think-you-know-excel-take-your-analytics-skills-to-the-next-level-with-power-query-930e2267006e","status":"publish","type":"post","link":"https:\/\/mailitics.com\/index.php\/2024\/11\/30\/think-you-know-excel-take-your-analytics-skills-to-the-next-level-with-power-query-930e2267006e\/","title":{"rendered":"Think you Know Excel? Take Your Analytics Skills to the Next Level with Power Query!"},"content":{"rendered":"<p>    Think you Know Excel? Take Your Analytics Skills to the Next Level with Power Query!<br \/>\n \t<BR><br \/>\n<BR><\/BR><br \/>\n    <!-- no image --><br \/>\n \t<BR><br \/>\n<BR><\/BR><\/p>\n<div>\n<h4>5 practical use cases that prove Power Query is worth exploring.<\/h4>\n<p>I have a confession to make: I\u2019ve been living under a rock \ud83e\udea8. Not literally, but how else can I explain not discovering <strong>Power Query<\/strong> in Excel until\u00a0now?<\/p>\n<p>Imagine realising that all those hours spent wrangling VLOOKUPs, nesting IFs, and battling messy data could\u2019ve been replaced with a few simple\u00a0clicks.<\/p>\n<figure><img data-recalc-dims=\"1\" decoding=\"async\" alt=\"\" src=\"https:\/\/i0.wp.com\/cdn-images-1.medium.com\/max\/712\/1%2AqzR1tyW9-KTPpuoAjFiCtw.png?ssl=1\"><figcaption>Image generated by the\u00a0author<\/figcaption><\/figure>\n<p>Power Query does everything Excel formulas could do\u200a\u2014\u200aonly faster, smarter, and way less frustrating. From merging datasets to effortless transformations and creating calculated columns, the possibilities are\u00a0endless.<\/p>\n<p>There are already tons of articles and videos out there with step-by-step guides to help you get started, so <strong>I won\u2019t write another how-to<\/strong>. Instead, I\u2019ll share the features that completely blew my mind with <strong>5 use cases\u200a<\/strong>\u2014\u200aand hopefully inspire you to dive in and explore this powerful tool yourself. \ud83d\ude80<\/p>\n<p>To demonstrate the remarkable functionality, I will use a simple e-commerce dataset consisting of two CSV files: one with customer data and the other with transaction data.<\/p>\n<figure><img data-recalc-dims=\"1\" decoding=\"async\" alt=\"\" src=\"https:\/\/i0.wp.com\/cdn-images-1.medium.com\/max\/221\/1%2AkkRGjLEAPNyilKjeMHq71A.png?ssl=1\"><\/figure>\n<figure><img data-recalc-dims=\"1\" decoding=\"async\" alt=\"\" src=\"https:\/\/i0.wp.com\/cdn-images-1.medium.com\/max\/651\/1%2A0cuhWV5ShsnggosPaC0xcg.png?ssl=1\"><figcaption>These screens display samples of the first 10 rows from each CSV file (generated by the\u00a0author)<\/figcaption><\/figure>\n<h3>Use Case 1: Merging Datasets (A Smarter Alternative to\u00a0VLOOKUP)<\/h3>\n<p>When it comes to combining data, we all turn to VLOOKUP. But let\u2019s face it\u200a\u2014\u200aVLOOKUP has its limitations.<\/p>\n<p><strong>Why Power Query is better than VLOOKUP\u2728:<\/strong><\/p>\n<ul>\n<li>You can join data from multiple columns in one step (no need for repeated lookups).<\/li>\n<li>Your lookup column doesn\u2019t have to be on the left\u200a\u2014\u200ait works no matter where it\u2019s\u00a0located.<\/li>\n<li>It supports different join types (left, right, full, inner, anti), giving you more control over how data is combined.<\/li>\n<li>Unlike VLOOKUP, Power Query doesn\u2019t limit you to joining on just one\u00a0column.<\/li>\n<li>You can even handle messy datasets with typos using fuzzy matching!<\/li>\n<\/ul>\n<p><strong>How to merge datasets in Power Query\u00a0\ud83d\udd17<\/strong>:<\/p>\n<ol>\n<li>While on the <strong>Home <\/strong>tab select <strong>\u201cMerge Queries\u201d.<\/strong>\n<\/li>\n<li>Choose the two datasets (or files) you want to\u00a0combine.<\/li>\n<li>Select the column to join on and specify the type of join (e.g., left join to keep all rows from one table, or inner join to match only overlapping rows).<\/li>\n<li>Once merged, select the columns you want to include in your final\u00a0dataset.<\/li>\n<li>Load the transformed data back into\u00a0Excel.<\/li>\n<\/ol>\n<p>Done in seconds!\u00a0\u23f1\ufe0f<\/p>\n<figure><img data-recalc-dims=\"1\" decoding=\"async\" alt=\"\" src=\"https:\/\/i0.wp.com\/cdn-images-1.medium.com\/max\/1024\/1%2AjhFeSRnda6Jdan4-9tiTQw.gif?ssl=1\"><figcaption>This gif explains the process of merging two files using Power Query (generated by the\u00a0author)<\/figcaption><\/figure>\n<h3>Use Case 2: Column Transformations<\/h3>\n<p>Power Query makes it super easy to transform your data. From quick calculations to date handling and creating bins, it does in seconds what could take minutes (or more) in\u00a0Excel.<\/p>\n<p>Let me show you how with a few examples. \ud83d\udc40<\/p>\n<h4>Perform Quick Calculations \u2795\u2796\u2797\u2716\ufe0f<\/h4>\n<p>Let\u2019s say you need to convert prices from USD to Euro and calculate the total value of your sales. In Power Query, you\u00a0can:<\/p>\n<ol>\n<li>Go to the <strong>\u201cTransform\u201d<\/strong> tab and, under \u201c<strong>Standard\u201d<\/strong>, select <strong>\u201cMultiply.\u201d<\/strong>\n<\/li>\n<li>Multiply all the prices by 0.95 to convert the currencies.<\/li>\n<li>Add a new column to calculate total sales by multiplying the price by the quantity. To do this, go to the <strong>\u201cAdd Column\u201d<\/strong> tab and select <strong>\u201cCustom\u00a0Column.\u201d<\/strong>\n<\/li>\n<\/ol>\n<p>All of this happens in just a few\u00a0clicks.<\/p>\n<figure><img data-recalc-dims=\"1\" decoding=\"async\" alt=\"\" src=\"https:\/\/i0.wp.com\/cdn-images-1.medium.com\/max\/1024\/1%2AO3XZwjP8N-GGPrftgaV9dQ.gif?ssl=1\"><figcaption>This gif explains the process of performing quick calculations using Power Query (generated by the\u00a0author)<\/figcaption><\/figure>\n<h4>Modify Dates with Ease\u00a0\ud83d\udcc5<\/h4>\n<p>Power Query makes working with dates straightforward too. You can quickly extract the month name or display only the first three letters (e.g., Jan, Feb, Mar) for a cleaner look using built-in functionality.<\/p>\n<ol>\n<li>Select the column that is in Date\u00a0format.<\/li>\n<li>Go to the <strong>\u201cAdd Column\u201d<\/strong> tab, and under <strong>\u201cDate\u201d,<\/strong> select<strong> \u201cMonth\u201d<\/strong> followed by <strong>\u201cName of the\u00a0Month\u201d<\/strong>.<\/li>\n<li>Select the new column that contains the name of the month. Under <strong>\u201cExtract\u201d<\/strong>, choose <strong>\u201cFirst Characters\u201d<\/strong>, and in the pop-up window, specify how many characters you want to extract (3 in this\u00a0case).<\/li>\n<\/ol>\n<p>No complicated formulas\u00a0needed!<\/p>\n<figure><img data-recalc-dims=\"1\" decoding=\"async\" alt=\"\" src=\"https:\/\/i0.wp.com\/cdn-images-1.medium.com\/max\/1024\/1%2ABGHm1tiBeFw0atEhdbXTvQ.gif?ssl=1\"><figcaption>This gif explains the process of modifying dates using Power Query (generated by the\u00a0author)<\/figcaption><\/figure>\n<h4>Create Bins Without Complex Formulas\u00a0\ud83d\udce5<\/h4>\n<p>I don\u2019t know about you, but I always forget the IF formula syntax for creating bins in Excel, and it can get pretty long if you have multiple ranges. But with Power Query, it\u2019s much\u00a0easier.<\/p>\n<ul>\n<li>\n<strong>If you\u2019re using Windows<\/strong>, you\u2019ve got the <strong>\u201cColumns from Examples\u201d<\/strong> feature. All you need to do is type one example of the bin range (e.g., \u201c41\u201350\u201d), and Power Query will automatically fill in the rest for you, saving you\u00a0time.<\/li>\n<li>\n<strong>For those of us on Mac <\/strong>(like me), unfortunately, this feature isn\u2019t available. But don\u2019t worry, there\u2019s a workaround! You can still create bins using the <strong>\u201cConditional Column\u201d<\/strong> feature by setting logic rules to categorize your data into\u00a0ranges.<\/li>\n<\/ul>\n<figure><img data-recalc-dims=\"1\" decoding=\"async\" alt=\"\" src=\"https:\/\/i0.wp.com\/cdn-images-1.medium.com\/max\/1024\/1%2AhaI8f7bEoc5_rlgefhGWzg.gif?ssl=1\"><figcaption>This gif explains the process of creating bins using the \u201cConditional Column\u201d feature (generated by the\u00a0author)<\/figcaption><\/figure>\n<h3>Use Case 3: Filling in Missing Data\u00a0\ud83d\udd0e<\/h3>\n<p>We\u2019ve all worked with datasets with missing values\u200a\u2014\u200awhether it is because of incomplete entries or data discrepancies. In most cases, you don\u2019t want to leave this gaps, instead you want to fill them in. This is where Power Query becomes particularly useful.<\/p>\n<p>Let\u2019s say we have missing values in the \u201cPrice per Unit\u201d column for the \u201cBeauty\u201d category, and we want to replace those missing values with the average price for that category. Here\u2019s how you can do it in a few simple\u00a0steps:<\/p>\n<ol>\n<li>Filter the \u201cProduct Category\u201d column to show only \u201cBeauty\u201d\u00a0entries.<\/li>\n<li>With the \u201cPrice per Unit\u201d column selected, check the average price for the Beauty category in the statistics section at the bottom of the\u00a0screen.<\/li>\n<li>Remove the filter on the \u201cProduct Category\u201d column to show all categories again.<\/li>\n<li>Select the \u201cPrice per Unit\u201d column again, go to the <strong>\u201cTransform\u201d<\/strong> tab, and click on <strong>\u201cReplace\u00a0Values\u201d<\/strong>.<\/li>\n<li>In the dialog box, enter \u201cnull\u201d for the value you want to replace and the average price for the Beauty category in the \u201cReplace With\u201d\u00a0field.<\/li>\n<\/ol>\n<p>And just like that, you\u2019ve filled in the missing data with the average value\u200a\u2014\u200aall in a few\u00a0clicks.<\/p>\n<figure><img data-recalc-dims=\"1\" decoding=\"async\" alt=\"\" src=\"https:\/\/i0.wp.com\/cdn-images-1.medium.com\/max\/1024\/1%2AowZIQPpmbgERrZzdcnSttg.gif?ssl=1\"><figcaption>This gif explains the process of filling the missing values using Power Query (generated by the\u00a0author)<\/figcaption><\/figure>\n<h3>Use Case 4: Transforming Data for Better Analysis\u00a0\ud83d\udee0\ufe0f<\/h3>\n<p>Power Query is great for transforming your data into a format to match the needs of your analysis.<\/p>\n<p>For example, if you want to summarise total sales per month and see the trend over time. You can use the \u201c<strong>Group By\u201d<\/strong> and \u201c<strong>Transpose\u201d<\/strong> functions.<\/p>\n<p>Here\u2019s how to do it in just 4\u00a0steps:<\/p>\n<ol>\n<li>Select the \u201cMonth\u201d column to group your data by\u00a0month.<\/li>\n<li>In the Transform tab, click on <strong>\u201cGroup\u00a0By\u201d<\/strong>.<\/li>\n<li>Add a new aggregation for \u201cTotal Value Euro\u201d and select Sum to calculate the total sales for each\u00a0month.<\/li>\n<li>Finally, click on <strong>\u201cTranspose\u201d<\/strong> to switch rows and columns, turning months into column\u00a0headers.<\/li>\n<\/ol>\n<p>Once you\u2019re done, load the data back into Excel and build your line chart to visualize the sales trends over\u00a0time!<\/p>\n<figure><img data-recalc-dims=\"1\" decoding=\"async\" alt=\"\" src=\"https:\/\/i0.wp.com\/cdn-images-1.medium.com\/max\/1024\/1%2AsRpHCcYw3h6r0lDHZ6sOvQ.gif?ssl=1\"><figcaption>This gif explains the process of using Group By and Transpose functions (generated by the\u00a0author)<\/figcaption><\/figure>\n<h3>Use Case 5. Custom Columns with M Formula Language\u00a0\ud83e\ude84<\/h3>\n<p>M formula language lets you go beyond the typical Power Query transformations, allowing for more advanced calculations and logic. It\u2019s perfect when you need to create custom solutions for your\u00a0data.<\/p>\n<p>For example, let\u2019s say the months in your sales data aren\u2019t sorted correctly. Instead of manually rearranging them, you can use <strong>M formulas <\/strong>to assign a numerical value to each month, then sort them in the right\u00a0order.<\/p>\n<ol>\n<li>Go to <strong>\u201cTransform\u201d<\/strong> and select <strong>\u201cCustom column\u201d\u00a0<\/strong>option.<\/li>\n<li>In the pop up window type the logic using \u201cif-else if\u201d logic to assign a numerical value to each\u00a0month.<\/li>\n<li>Sort by the new column in ASC\u00a0order.<\/li>\n<li>You can even remove the \u201cMonth Order\u201d column once the sorting is\u00a0done.<\/li>\n<\/ol>\n<p>After doing that, your months will be in the right order.\u00a0\ud83c\udfc6\ud83c\udfc6\ud83c\udfc6<\/p>\n<pre>if [Month short] = \"Jan\" then 1<br>else if [Month short] = \"Feb\" then 2<br>else if [Month short] = \"Mar\" then 3<br>else if [Month short] = \"Apr\" then 4<br>else if [Month short] = \"May\" then 5<br>else if [Month short] = \"Jun\" then 6<br>else if [Month short] = \"Jul\" then 7<br>else if [Month short] = \"Aug\" then 8<br>else if [Month short] = \"Sep\" then 9<br>else if [Month short] = \"Oct\" then 10<br>else if [Month short] = \"Nov\" then 11<br>else if [Month short] = \"Dec\" then 12<br>else null<\/pre>\n<figure><img data-recalc-dims=\"1\" decoding=\"async\" alt=\"\" src=\"https:\/\/i0.wp.com\/cdn-images-1.medium.com\/max\/1024\/1%2AO9P3xV7Gwbfrjb9Rq_eCeg.gif?ssl=1\"><figcaption>This gif explains the process of using M formula language in Power Query (generated by the\u00a0author)<\/figcaption><\/figure>\n<h3>Last but not the\u00a0least\u2026<\/h3>\n<p>Power Query keeps track of every change in an applied steps log \ud83d\udcbe, so if you want to go back and modify\/undo anything, it\u2019s super\u00a0easy.<\/p>\n<h4>Feeling inspired? \ud83e\uddd9\u200d\u2642\ufe0f<\/h4>\n<p>I hope you\u2019re now as excited to try these features as I was! If I were you, I\u2019d be jumping into Power Query right\u00a0away.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/medium.com\/_\/stat?event=post.clientViewed&amp;referrerSource=full_rss&amp;postId=930e2267006e\" width=\"1\" height=\"1\" alt=\"\"><\/p>\n<hr>\n<p><a href=\"https:\/\/towardsdatascience.com\/think-you-know-excel-take-your-analytics-skills-to-the-next-level-with-power-query-930e2267006e\">Think you Know Excel? Take Your Analytics Skills to the Next Level with Power Query!<\/a> was originally published in <a href=\"https:\/\/towardsdatascience.com\/\">Towards Data Science<\/a> on Medium, where people are continuing the conversation by highlighting and responding to this story.<\/p>\n<\/div>\n<p> \t<BR><br \/>\n <BR><\/BR><br \/>\n    Ilona Hetsevich<br \/>\n \t<BR><br \/>\n<BR><\/BR><br \/>\n<a href=\"https:\/\/medium.com\/m\/global-identity-2?redirectUrl=https%3A%2F%2Ftowardsdatascience.com%2Fthink-you-know-excel-take-your-analytics-skills-to-the-next-level-with-power-query-930e2267006e\">Go to original source<\/a><br \/>\n \t<BR><br \/>\n <BR><\/BR><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Think you Know Excel? Take Your Analytics Skills to the Next Level with Power Query! 5 practical use cases that prove Power Query is worth exploring. I have a confession to make: I\u2019ve been living under a rock \ud83e\udea8. Not literally, but how else can I explain not discovering Power Query in Excel until\u00a0now? Imagine [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[62,211,243,83,245,244],"tags":[84,122,246],"class_list":["post-280","post","type-post","status-publish","format-standard","hentry","category-aimldsaimlds","category-data-analysis","category-data-analytics","category-data-science","category-excel","category-power-query","tag-data","tag-power","tag-query"],"_links":{"self":[{"href":"https:\/\/mailitics.com\/index.php\/wp-json\/wp\/v2\/posts\/280"}],"collection":[{"href":"https:\/\/mailitics.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mailitics.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mailitics.com\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/mailitics.com\/index.php\/wp-json\/wp\/v2\/comments?post=280"}],"version-history":[{"count":0,"href":"https:\/\/mailitics.com\/index.php\/wp-json\/wp\/v2\/posts\/280\/revisions"}],"wp:attachment":[{"href":"https:\/\/mailitics.com\/index.php\/wp-json\/wp\/v2\/media?parent=280"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mailitics.com\/index.php\/wp-json\/wp\/v2\/categories?post=280"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mailitics.com\/index.php\/wp-json\/wp\/v2\/tags?post=280"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}