{"id":6096,"date":"2017-04-29T09:55:30","date_gmt":"2017-04-29T07:55:30","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=6096"},"modified":"2024-02-06T16:03:34","modified_gmt":"2024-02-06T15:03:34","slug":"replicating-excels-find-function-with-m-in-power-query","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/replicating-excels-find-function-with-m-in-power-query\/","title":{"rendered":"Replicating Excel&#8217;s FIND 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.\u00a0 There are times when we want to do things that are not built in the user interface.\u00a0 This is possible with <strong>Power Query&#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=\"formula\" data-wpil-keyword-link=\"linked\">formula<\/a> language, which is called 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 <strong>Excel&#8217;s FIND Function\u00a0<\/strong>to find a specific character in text<strong>,<\/strong> it is not supported in\u00a0<strong>M<\/strong>.<\/p>\n<p>Let me show you how I can replicate the <strong>FIND\u00a0Function\u00a0<\/strong>in M!<\/p>\n<div class=\"after-post-box\">\n<p><iframe src=\"https:\/\/www.youtube.com\/embed\/Hkn4Aztr9hE?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\/03\/Replicating-Excels-FIND-Function-with-M.xlsx\" target=\"_blank\" rel=\"noopener noreferrer\" data-leadbox=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/03\/Replicating-Excels-FIND-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-FIND-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\/03\/Replicating-Excels-FIND-Function-with-M-01.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Replicating Excel&#039;s FIND Function with M in Power Query\"  class=\"alignnone size-full wp-image-6097\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/03\/Replicating-Excels-FIND-Function-with-M-01.jpg\" alt=\"Replicating Excel&#039;s FIND Function with M in Power Query\" width=\"255\" height=\"372\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/03\/Replicating-Excels-FIND-Function-with-M-01.jpg 255w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/03\/Replicating-Excels-FIND-Function-with-M-01-206x300.jpg 206w\" sizes=\"(max-width: 255px) 100vw, 255px\" \/><\/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 position of the letter O\u00a0<\/strong>of 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\/03\/Replicating-Excels-FIND-Function-with-M-02.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Replicating Excel&#039;s FIND Function with M in Power Query\"  class=\"size-full wp-image-6098 alignnone\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/03\/Replicating-Excels-FIND-Function-with-M-02.jpg\" alt=\"Replicating Excel&#039;s FIND Function with M in Power Query\" width=\"359\" height=\"386\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/03\/Replicating-Excels-FIND-Function-with-M-02.jpg 359w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/03\/Replicating-Excels-FIND-Function-with-M-02-279x300.jpg 279w\" sizes=\"(max-width: 359px) 100vw, 359px\" \/><\/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>FIND\u00a0function in Excel<\/strong><strong>.<\/strong><\/p>\n<p>In the <strong>New column name <\/strong>text box, type<span style=\"color: #0000ff;\"><em>\u00a0<b>FIND<\/b><\/em><\/span><\/p>\n<p>In the <strong>Custom column formula<\/strong>, type in:<strong><span style=\"color: #0000ff;\"><em> Text.PositionOf(<\/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;\">&#8220;o&#8221;) + 1\u00a0<\/span><\/em><\/strong><\/p>\n<p>We now have built 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-find-function-with-m-in-power-query\/#TextPositionOfCHANNEL_PARTNERS_%E2%80%9Co%E2%80%9D_1\" title=\"Text.PositionOf([CHANNEL PARTNERS], &#8220;o&#8221;) + 1\">Text.PositionOf([CHANNEL PARTNERS], &#8220;o&#8221;) + 1<\/a><\/li><\/ul><\/nav><\/div>\n<h2 style=\"text-align: center;\"><span class=\"ez-toc-section\" id=\"TextPositionOfCHANNEL_PARTNERS_%E2%80%9Co%E2%80%9D_1\"><\/span><span style=\"color: #0000ff;\"><strong><em>Text.PositionOf([CHANNEL PARTNERS], &#8220;o&#8221;) + 1<\/em><\/strong><\/span><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>&nbsp;<\/p>\n<p>So let us quickly break down what we just did:<\/p>\n<ul>\n<li>We are using the <strong>Text.PositionOf\u00a0<\/strong>formula to\u00a0get the find the letter &#8220;o&#8221; in\u00a0the <b>CHANNEL PARTNERS<\/b>\u00a0column<\/li>\n<li>We added 1 to it because the <em>PositionOf<\/em> formula starts counting at 0.\u00a0\u00a0 That is different to the FIND formula in Excel which starts counting at 1 as the 1st character.<\/li>\n<li>So to replicate the FIND formula, we need to add 1 to our formula to make up for the difference.<\/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\/03\/Replicating-Excels-FIND-Function-with-M-03.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Replicating Excel&#039;s FIND Function with M in Power Query\"  class=\"alignnone size-full wp-image-6099\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/03\/Replicating-Excels-FIND-Function-with-M-03.jpg\" alt=\"Replicating Excel&#039;s FIND Function with M in Power Query\" width=\"699\" height=\"412\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/03\/Replicating-Excels-FIND-Function-with-M-03.jpg 699w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/03\/Replicating-Excels-FIND-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.\u00a0 For example, in &#8220;Widget Corp&#8221; the first &#8220;o&#8221; encountered is the 9th character in this text string.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/03\/Replicating-Excels-FIND-Function-with-M-04.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Replicating Excel&#039;s FIND Function with M in Power Query\"  class=\"alignnone size-full wp-image-6100\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/03\/Replicating-Excels-FIND-Function-with-M-04.jpg\" alt=\"Replicating Excel&#039;s FIND Function with M in Power Query\" width=\"291\" height=\"219\" \/><\/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\/03\/Replicating-Excels-FIND-Function-with-M-05.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Replicating Excel&#039;s FIND Function with M in Power Query\"  class=\"alignnone size-full wp-image-6101\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/03\/Replicating-Excels-FIND-Function-with-M-05.jpg\" alt=\"Replicating Excel&#039;s FIND Function with M in Power Query\" width=\"353\" height=\"128\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/03\/Replicating-Excels-FIND-Function-with-M-05.jpg 353w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/03\/Replicating-Excels-FIND-Function-with-M-05-300x109.jpg 300w\" sizes=\"(max-width: 353px) 100vw, 353px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>Congratulations!\u00a0 You have used the <strong>M <\/strong>formula in Power Query to replicate Excel&#8217;s <strong>FIND\u00a0<\/strong><strong>function<\/strong>!<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/03\/Replicating-Excels-FIND-Function-with-M-06-1.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Replicating Excel&#039;s FIND Function with M in Power Query\"  class=\"alignnone size-full wp-image-6104\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/03\/Replicating-Excels-FIND-Function-with-M-06-1.jpg\" alt=\"Replicating Excel&#039;s FIND Function with M in Power Query\" width=\"205\" height=\"205\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/03\/Replicating-Excels-FIND-Function-with-M-06-1.jpg 205w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/03\/Replicating-Excels-FIND-Function-with-M-06-1-150x150.jpg 150w\" sizes=\"(max-width: 205px) 100vw, 205px\" \/><\/a><\/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.\u00a0 There are times when we want to do things that are not built in the user interface.\u00a0 This is possible with Power Query&#8217;s formula language, which is called M. Unfortunately not all of Excel&#8217;s formulas can be used in M. For [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":17306,"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":[121,120],"tags":[239,216,117],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/6096"}],"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=6096"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/6096\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17306"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=6096"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=6096"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=6096"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}