{"id":5949,"date":"2017-03-09T17:51:00","date_gmt":"2017-03-09T16:51:00","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=5949"},"modified":"2024-02-06T16:03:31","modified_gmt":"2024-02-06T15:03:31","slug":"replicating-excels-len-function-with-m-in-power-query","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/replicating-excels-len-function-with-m-in-power-query\/","title":{"rendered":"Replicating Excel&#8217;s LEN 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>Power Query&#8217;s programming language, which is M.<\/strong><\/p>\n<p>Unfortunately not all of Excel&#8217;s <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/top-excel-formulas-function-examples-to-get-better-at-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\" title=\"formulas\" data-wpil-keyword-link=\"linked\">formulas<\/a> 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\/category\/formulas\/text\/len\/\" target=\"_blank\" rel=\"noopener\">LEN\u00a0Excel Function<\/a>\u00a0<\/strong>to get the length of strings<strong>,<\/strong> it is not supported in\u00a0<strong>M<\/strong>.<\/p>\n<p>Let me show you how I can replicate the\u00a0<strong>LEN Function\u00a0<\/strong>in M!<\/p>\n<div class=\"after-post-box\">\n<p><iframe src=\"https:\/\/www.youtube.com\/embed\/PycbGIh4E_U?rel=0 \" width=\"800\" height=\"400\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\"><span data-mce-type=\"bookmark\" style=\"display: inline-block; width: 0px; overflow: hidden; line-height: 0;\" class=\"mce_SELRES_start\">\ufeff<\/span><\/iframe><a href=\"https:\/\/www.youtube.com\/c\/MyExcelOnlinecomtutorials?sub_confirmation=1\" target=\"_blank\" rel=\"noopener noreferrer nofollow\"><img loading=\"lazy\" decoding=\"async\" class=\"alignmiddle wp-image-111468 size-full\" style=\"vertical-align: middle;\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/04\/1.yt_.png\" alt=\"\" title=\"\"><\/a><\/p>\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-LEN-Function-with-M.xlsx\" target=\"_blank\" rel=\"noopener noreferrer\" data-leadbox=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/02\/Replicating-Excels-LEN-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-LEN-Function-with-M.xlsx<\/span><\/a><\/p>\n<p class=\"line-two\">\n<\/div>\n<p>&nbsp;<\/p>\n<p>Let\u2019s go through the steps in detail:<\/p>\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\/2017\/02\/Replicating-Excels-LEN-Function-with-M-01.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Replicating Excel&#039;s LEN Function with M in Power Query\"  class=\"alignnone size-full wp-image-5965\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/02\/Replicating-Excels-LEN-Function-with-M-01.png\" alt=\"Replicating Excel&#039;s LEN 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\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/11\/Merge-Columns-02.png\" alt=\"merge-columns-02\" 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\" title=\"\"><\/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>We want to <strong>get the length<\/strong>\u00a0of the <strong>Channel Partners<\/strong>, so we need to select the CHANNEL PARTNERS column.<\/p>\n<p>Go to <em><strong>Add Column &gt; Add Custom Column<\/strong><\/em><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/02\/Replicating-Excels-LEN-Function-with-M-02.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Replicating Excel&#039;s LEN Function with M in Power Query\"  class=\"alignnone size-full wp-image-5966\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/02\/Replicating-Excels-LEN-Function-with-M-02.jpg\" alt=\"Replicating Excel&#039;s LEN Function with M in Power Query\" width=\"1099\" height=\"713\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/02\/Replicating-Excels-LEN-Function-with-M-02.jpg 1099w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/02\/Replicating-Excels-LEN-Function-with-M-02-300x195.jpg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/02\/Replicating-Excels-LEN-Function-with-M-02-1024x664.jpg 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/02\/Replicating-Excels-LEN-Function-with-M-02-768x498.jpg 768w\" sizes=\"(max-width: 1099px) 100vw, 1099px\" \/><\/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>LEN\u00a0function in Excel<\/strong><strong>.<\/strong><\/p>\n<p>In the <strong>New column name <\/strong>text box, type<span style=\"color: #0000ff;\"><em> <strong>CHANNEL PARTNERS<\/strong><strong>\u00a0(LEN)<\/strong><\/em><\/span><\/p>\n<p>In the <strong>Custom column <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/top-excel-formulas-function-examples-to-get-better-at-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\" title=\"formula\" data-wpil-keyword-link=\"linked\">formula<\/a><\/strong>, type in:<strong><span style=\"color: #0000ff;\"><em> Text.Length(<\/em><\/span><br \/>\n<\/strong><\/p>\n<p>From the <strong>Available columns<\/strong> choose <span style=\"color: #0000ff;\"><strong>CHANNEL PARTNERS<\/strong><\/span>\u00a0and select Insert.<\/p>\n<p>Then finish off the formula by entering \u00a0<strong><em><span style=\"color: #0000ff;\">)\u00a0<\/span><\/em><\/strong><\/p>\n<p>We now have build the following formula:<\/p>\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-len-function-with-m-in-power-query\/#TextLengthCHANNEL_PARTNERS\" title=\"Text.Length([CHANNEL PARTNERS])\">Text.Length([CHANNEL PARTNERS])<\/a><\/li><\/ul><\/nav><\/div>\n<h2 style=\"text-align: center;\"><span class=\"ez-toc-section\" id=\"TextLengthCHANNEL_PARTNERS\"><\/span><span style=\"color: #0000ff;\"><strong><em>Text.Length([CHANNEL PARTNERS])<\/em><\/strong><\/span><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>So lets quickly break down what we just did:<\/p>\n<ul>\n<li>We are using the <strong>Text.Length\u00a0<\/strong>formula to\u00a0get the length\u00a0of the <b>CHANNEL PARTNERS<\/b>\u00a0column<\/li>\n<li>Click <strong>OK to confirm.<\/strong><\/li>\n<\/ul>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/02\/Replicating-Excels-LEN-Function-with-M-03.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Replicating Excel&#039;s LEN Function with M in Power Query\"  class=\"alignnone size-full wp-image-5967\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/02\/Replicating-Excels-LEN-Function-with-M-03.jpg\" alt=\"Replicating Excel&#039;s LEN Function with M in Power Query\" width=\"699\" height=\"412\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/02\/Replicating-Excels-LEN-Function-with-M-03.jpg 699w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/02\/Replicating-Excels-LEN-Function-with-M-03-300x177.jpg 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\/2017\/02\/Replicating-Excels-LEN-Function-with-M-04.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Replicating Excel&#039;s LEN Function with M in Power Query\"  class=\"alignnone size-full wp-image-5968\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/02\/Replicating-Excels-LEN-Function-with-M-04.jpg\" alt=\"Replicating Excel&#039;s LEN Function with M in Power Query\" width=\"1065\" height=\"595\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/02\/Replicating-Excels-LEN-Function-with-M-04.jpg 1065w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/02\/Replicating-Excels-LEN-Function-with-M-04-300x168.jpg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/02\/Replicating-Excels-LEN-Function-with-M-04-1024x572.jpg 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/02\/Replicating-Excels-LEN-Function-with-M-04-768x429.jpg 768w\" sizes=\"(max-width: 1065px) 100vw, 1065px\" \/><\/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\/2017\/02\/Replicating-Excels-LEN-Function-with-M-05.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Replicating Excel&#039;s LEN Function with M in Power Query\"  class=\"alignnone size-full wp-image-5969\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/02\/Replicating-Excels-LEN-Function-with-M-05.png\" alt=\"Replicating Excel&#039;s LEN Function with M in Power Query\" width=\"352\" height=\"120\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/02\/Replicating-Excels-LEN-Function-with-M-05.png 352w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/02\/Replicating-Excels-LEN-Function-with-M-05-300x102.png 300w\" sizes=\"(max-width: 352px) 100vw, 352px\" \/><\/a><\/p>\n<p>Congratulations! You have used a <strong>M <\/strong>formula for replicating the <strong>LEN\u00a0<\/strong><strong>function<\/strong>!<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/02\/Replicating-Excels-LEN-Function-with-M-06.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Replicating Excel&#039;s LEN Function with M in Power Query\"  class=\"alignnone size-full wp-image-5970\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/02\/Replicating-Excels-LEN-Function-with-M-06.jpg\" alt=\"Replicating Excel&#039;s LEN Function with M in Power Query\" width=\"620\" height=\"517\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/02\/Replicating-Excels-LEN-Function-with-M-06.jpg 620w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/02\/Replicating-Excels-LEN-Function-with-M-06-300x250.jpg 300w\" sizes=\"(max-width: 620px) 100vw, 620px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/100-21.html\" target=\"_blank\" rel=\"noopener noreferrer\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-5921 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/03\/530x300-PQ.jpg\" width=\"530\" height=\"300\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/03\/530x300-PQ.jpg 530w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/03\/530x300-PQ-300x170.jpg 300w\" sizes=\"(max-width: 530px) 100vw, 530px\" alt=\"\" title=\"\"><\/a><\/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":17363,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"Replicating Excel's LEN Function with M in Power Query","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[120,121],"tags":[232,216,117],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/5949"}],"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=5949"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/5949\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17363"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=5949"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=5949"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=5949"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}