{"id":5725,"date":"2017-02-02T17:55:35","date_gmt":"2017-02-02T16:55:35","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=5725"},"modified":"2024-03-22T19:06:45","modified_gmt":"2024-03-22T18:06:45","slug":"replicating-excels-left-function-m-in-power-query","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/replicating-excels-left-function-m-in-power-query\/","title":{"rendered":"Replicating Excel&#8217;s LEFT Function with M in Power Query"},"content":{"rendered":"<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/installing-power-query-excel-2010\/\" target=\"_blank\" rel=\"noopener noreferrer\">Power Query<\/a> lets you perform a series of steps to transform your Excel data. There are times when we want to do things that are not built in the user interface. This is possible with <strong><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/50-things-you-can-do-with-excel-power-query\/\" target=\"_blank\" rel=\"noopener\">Power Query&#8217;s programming language,<\/a> which is M.<\/strong><\/p>\n<p>Unfortunately not all of Excel&#8217;s formulas can be used in M.<\/p>\n<p>For example,\u00a0if we want to use the\u00a0<strong><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/extracting-data-with-excels-left-formula\/\" target=\"_blank\" rel=\"noopener\">LEFT Excel<\/a> Function<\/strong><strong>,<\/strong> it is not supported in\u00a0<strong>M<\/strong>.<\/p>\n<p>But I have found a way for us to replicate the\u00a0<strong>LEFT Function\u00a0<\/strong>in M!<\/p>\n\n<div class=\"after-post-box\">\n<p class=\"line-two\"><a class=\"IRPP_minimalist icon-download button small\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/02\/Replicating-Excels-LEFT-Function-with-M.xlsx\" target=\"_blank\" rel=\"noopener noreferrer\" data-leadbox=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/02\/Replicating-Excels-LEFT-Function-with-M.xlsx\" data-leadbox-id=\"147284833f72a2:12f08263ab46dc\"><span class=\"ctaText px-3 text-capitalize\">Download excel workbook<i class=\"fa fa-download\"><\/i><\/span><span class=\"postTitle px-3\">Replicating-Excels-LEFT-Function-with-M.xlsx<\/span><\/a><\/p>\n<\/div>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_57_1 counter-flat ez-toc-counter ez-toc-transparent ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title \" >Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li><a href=\"#\" class=\"scroll-to-download\">Free Practice Workbook<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/replicating-excels-left-function-m-in-power-query\/#Step_By_Step_Guide\" title=\"Step By Step Guide\">Step By Step Guide<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/replicating-excels-left-function-m-in-power-query\/#Conclusion\" title=\"Conclusion\">Conclusion<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"Step_By_Step_Guide\"><\/span>Step By Step Guide<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"color: #ff0000;\"><strong>STEP 1<\/strong>:<\/span> Select\u00a0your data and turn it into an <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/how-to-insert-an-excel-table\/\" target=\"_blank\" rel=\"noopener noreferrer\">Excel Table<\/a> by pressing the shortcut <strong>Ctrl + T <\/strong>or by going to<strong> Insert &gt; Table<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Replicating-Excels-LEFT-Function-with-M-01.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-5727\" title=\"Replicating Excel's LEFT Function with M in Power Query\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Replicating-Excels-LEFT-Function-with-M-01.png\" alt=\"Replicating Excel's LEFT Function with M in Power Query\" width=\"243\" height=\"284\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 2<\/strong>:<\/span> Go to <strong><em>Data &gt; Get &amp; Transform &gt; From Table (Excel 2016) <\/em><\/strong><em>or<\/em><strong><em> Power Query &gt; Excel Data &gt; From Table (Excel 2013 &amp; 2010)<\/em><\/strong><\/p>\n<p><span style=\"color: #0000ff;\"><strong>Excel 2016:<\/strong><\/span><\/p>\n<p><strong><em>\u00a0<a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Merge-Columns-02.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-5431\" title=\"Merge Columns Using Power Query\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Merge-Columns-02.png\" alt=\"Merge Columns Using Power Query\" width=\"618\" height=\"132\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Merge-Columns-02.png 618w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Merge-Columns-02-300x64.png 300w\" sizes=\"(max-width: 618px) 100vw, 618px\" \/><\/a><\/em><\/strong><\/p>\n<p><span style=\"color: #0000ff;\"><strong>Excel 2013 &amp; 2010:<\/strong><\/span><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/from-table.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-3239\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/from-table.png\" alt=\"from table\" width=\"777\" height=\"129\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/from-table.png 777w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/from-table-300x50.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/from-table-768x128.png 768w\" sizes=\"(max-width: 777px) 100vw, 777px\" title=\"\"><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 3:<\/span> <\/strong>This will open up the <strong>Power Query Editor.<\/strong><\/p>\n<p>Go to <em><strong>Add Column &gt; Add Custom Column<br \/>\n<\/strong><\/em><\/p>\n<p>We want to <strong>get the first 3 characters<\/strong> of the <strong>Sales Month<\/strong>:<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Replicating-Excels-LEFT-Function-with-M-02.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-5728\" title=\"Replicating Excel's LEFT Function with M in Power Query\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Replicating-Excels-LEFT-Function-with-M-02.png\" alt=\"Replicating Excel's LEFT Function with M in Power Query\" width=\"755\" height=\"463\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Replicating-Excels-LEFT-Function-with-M-02.png 755w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Replicating-Excels-LEFT-Function-with-M-02-300x184.png 300w\" sizes=\"(max-width: 755px) 100vw, 755px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 4: <\/span><\/strong>Let us create a simple <strong>M <\/strong>expression to replicate the\u00a0<strong>LEFT function in Excel<\/strong><strong>.<\/strong><\/p>\n<p>In the <strong>New column name <\/strong>text box, type<span style=\"color: #0000ff;\"><em> <strong>SALES MONTH (Shortened)<\/strong><\/em><\/span><\/p>\n<p>In the <strong>Custom column formula<\/strong>, type in:<strong><span style=\"color: #0000ff;\"><em> Text.Start(<\/em><\/span><br \/>\n<\/strong><\/p>\n<p>From the <strong>Available columns<\/strong> choose <span style=\"color: #0000ff;\"><strong>SALES MONTH<\/strong><\/span> and Insert<\/p>\n<p>Then finish off the formula by entering\u00a0<strong><em><span style=\"color: #0000ff;\">, 3)\u00a0<\/span><\/em><\/strong><\/p>\n<p>We now have build the following formula:<\/p>\n<h3 style=\"text-align: center;\"><span style=\"color: #0000ff;\"><strong><em>Text.Start([SALES MONTH], 3)<\/em><\/strong><\/span><\/h3>\n<p>&nbsp;<\/p>\n<p>So lets quickly break down what we just did:<\/p>\n<ul>\n<li>We are using the <strong>Text.Start\u00a0<\/strong>formula to\u00a0get the first <strong>X<\/strong>\u00a0characters of the <strong>SALES MONTH<\/strong> column<\/li>\n<li>We place in <strong>3<\/strong>, to specify that we want the\u00a0<strong>first 3 characters.<\/strong><\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>Click <strong>OK to confirm.<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Replicating-Excels-LEFT-Function-with-M-03.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-5729\" title=\"Replicating Excel's LEFT Function with M in Power Query\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Replicating-Excels-LEFT-Function-with-M-03.png\" alt=\"Replicating Excel's LEFT Function with M in Power Query\" width=\"699\" height=\"412\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Replicating-Excels-LEFT-Function-with-M-03.png 699w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Replicating-Excels-LEFT-Function-with-M-03-300x177.png 300w\" sizes=\"(max-width: 699px) 100vw, 699px\" \/><\/a><\/p>\n<p>Now you will see your changes take place.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Replicating-Excels-LEFT-Function-with-M-04.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-5730\" title=\"Replicating Excel's LEFT Function with M in Power Query\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Replicating-Excels-LEFT-Function-with-M-04.png\" alt=\"Replicating Excel's LEFT Function with M in Power Query\" width=\"1074\" height=\"667\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Replicating-Excels-LEFT-Function-with-M-04.png 1074w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Replicating-Excels-LEFT-Function-with-M-04-300x186.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Replicating-Excels-LEFT-Function-with-M-04-1024x636.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Replicating-Excels-LEFT-Function-with-M-04-768x477.png 768w\" sizes=\"(max-width: 1074px) 100vw, 1074px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 5: <\/strong><\/span>Click <strong>Close &amp; Load<\/strong> from the <strong>Home<\/strong> tab and this will <strong>open up a brand new worksheet<\/strong> in your Excel workbook with the updated values.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Replicating-Excels-LEFT-Function-with-M-05.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-5731\" title=\"Replicating Excel's LEFT Function with M in Power Query\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Replicating-Excels-LEFT-Function-with-M-05.png\" alt=\"Replicating Excel's LEFT Function with M in Power Query\" width=\"352\" height=\"120\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Replicating-Excels-LEFT-Function-with-M-05.png 352w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Replicating-Excels-LEFT-Function-with-M-05-300x102.png 300w\" sizes=\"(max-width: 352px) 100vw, 352px\" \/><\/a><\/p>\n<p>Congratulations! You have used a <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/free-microsoft-excel-online-course\/\" target=\"_blank\" rel=\"noopener\"><strong>M <\/strong>formula<\/a> for replicating the <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/top-excel-formulas-function-examples-to-get-better-at-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\"><strong>LEFT\u00a0<\/strong><strong>function<\/strong><\/a>!<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Replicating-Excels-LEFT-Function-with-M-06.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-5732\" title=\"Replicating Excel's LEFT Function with M in Power Query\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Replicating-Excels-LEFT-Function-with-M-06.png\" alt=\"Replicating Excel's LEFT Function with M in Power Query\" width=\"626\" height=\"403\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Replicating-Excels-LEFT-Function-with-M-06.png 626w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Replicating-Excels-LEFT-Function-with-M-06-300x193.png 300w\" sizes=\"(max-width: 626px) 100vw, 626px\" \/><\/a><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Conclusion\"><\/span>Conclusion<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><strong>Further Learning:<\/strong><\/p>\n<ul>\n<li><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/replicating-excels-right-function-with-m-in-power-query\/\" target=\"_blank\" rel=\"noopener\">Replicating Excel&#8217;s RIGHT Function with M in Power Query<\/a><\/li>\n<li><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/replicating-excels-find-function-with-m-in-power-query\/\" target=\"_blank\" rel=\"noopener\">Replicating Excel&#8217;s FIND Function with M in Power Query<\/a><\/li>\n<li><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/replicating-excels-len-function-with-m-in-power-query\/\" target=\"_blank\" rel=\"noopener\">Replicating Excel&#8217;s LEN Function with M in Power Query<\/a><\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Power Query lets you perform a series of steps to transform your Excel data. There are times when we want to do things that are not built in the user interface. This is possible with Power Query&#8217;s programming language, which is M. Unfortunately not all of Excel&#8217;s formulas can be used in M. For example,\u00a0if [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":17121,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[120,121],"tags":[166,216,117],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/5725"}],"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\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=5725"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/5725\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17121"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=5725"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=5725"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=5725"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}