{"id":5646,"date":"2016-12-15T18:12:21","date_gmt":"2016-12-15T17:12:21","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=5646"},"modified":"2024-02-06T16:03:21","modified_gmt":"2024-02-06T15:03:21","slug":"getting-started-with-m-in-power-query","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/getting-started-with-m-in-power-query\/","title":{"rendered":"Getting Started with M in Power Query"},"content":{"rendered":"<p>&nbsp;<\/p>\n<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.<\/p>\n<p>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>To start off, we will do\u00a0a simple example of merging the first name and second name into a new column. This is possible with the <strong>CONCATENATE <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>, however I want to use a simple example for you to get a feel of how to use <strong>M in Power Query<\/strong>. \u00a0Baby steps!<\/p>\n<div class=\"after-post-box\">\n<p><iframe src=\"https:\/\/www.youtube.com\/embed\/lYDVGlsuW7A?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\/2016\/12\/Getting-Started-with-M.xlsx\" target=\"_blank\" rel=\"noopener noreferrer\" data-leadbox=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Getting-Started-with-M.xlsx\" data-leadbox-id=\"147284833f72a2:12f08263ab46dc\"><span class=\"ctaText px-3 text-capitalize\">Download excel workbook <\/span><span class=\"postTitle px-3\">Getting-Started-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\/2016\/12\/Getting-Started-with-M-01.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Getting Started with M in Power Query\"  class=\"alignnone size-full wp-image-5649\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Getting-Started-with-M-01.png\" alt=\"Getting Started with M in Power Query\" width=\"241\" height=\"282\" \/><\/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\" title=\"Merge Columns Using Power Query\"  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 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>Here we will have our first taste of using <strong>M!<\/strong><\/p>\n<p>Go to <em><strong>Add Column &gt; Add Custom Column<br \/>\n<\/strong><\/em><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Getting-Started-with-M-02.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Getting Started with M in Power Query\"  class=\"alignnone size-full wp-image-5650\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Getting-Started-with-M-02.png\" alt=\"Getting Started with M in Power Query\" width=\"454\" height=\"388\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Getting-Started-with-M-02.png 454w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Getting-Started-with-M-02-300x256.png 300w\" sizes=\"(max-width: 454px) 100vw, 454px\" \/><\/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 combine the <strong>First Name <\/strong>and the <strong>Last Name.<\/strong><\/p>\n<p>In the <strong>New column name<\/strong> text box, type <em><strong>Full Name<\/strong><\/em><\/p>\n<p>In the <strong>Custom column formula<\/strong>, type in: <em><strong>[First Name]&amp;&#8221; &#8220;&amp;[Last Name]\u00a0<\/strong><\/em><strong><br \/>\n<\/strong><\/p>\n<p><em>(You can alternatively double click in the <strong>Available columns<\/strong> names to use the column names in the formula)<\/em><\/p>\n<p>The <strong>Ampersand (&amp;)<\/strong> will combine the values together, then we added a space in the middle with the <strong>double quotes<\/strong> <strong>&#8221; &#8220;<\/strong><\/p>\n<p>Click <strong>OK.<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Getting-Started-with-M-03.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Getting Started with M in Power Query\"  class=\"alignnone size-full wp-image-5651\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Getting-Started-with-M-03.png\" alt=\"Getting Started with M in Power Query\" width=\"699\" height=\"412\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Getting-Started-with-M-03.png 699w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Getting-Started-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\/Getting-Started-with-M-04.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Getting Started with M in Power Query\"  class=\"alignnone size-full wp-image-5652\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Getting-Started-with-M-04.png\" alt=\"Getting Started with M in Power Query\" width=\"508\" height=\"252\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Getting-Started-with-M-04.png 508w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Getting-Started-with-M-04-300x149.png 300w\" sizes=\"(max-width: 508px) 100vw, 508px\" \/><\/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\/Getting-Started-with-M-05.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Getting Started with M in Power Query\"  class=\"alignnone size-full wp-image-5653\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Getting-Started-with-M-05.png\" alt=\"Getting Started with M in Power Query\" width=\"352\" height=\"120\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Getting-Started-with-M-05.png 352w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Getting-Started-with-M-05-300x102.png 300w\" sizes=\"(max-width: 352px) 100vw, 352px\" \/><\/a><\/p>\n<p>Woohoo! You now had your first taste of programming using <strong>M!<\/strong> Watch out for future posts as we tackle on more complex <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>!<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Getting-Started-with-M-06.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Getting Started with M in Power Query\"  class=\"alignnone size-full wp-image-5654\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Getting-Started-with-M-06.png\" alt=\"Getting Started with M in Power Query\" width=\"382\" height=\"225\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Getting-Started-with-M-06.png 382w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/12\/Getting-Started-with-M-06-300x177.png 300w\" sizes=\"(max-width: 382px) 100vw, 382px\" \/><\/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\" title=\"Add Comma in Excel between Names with SUBSTITUTE Formula\"  class=\"alignnone wp-image-4486 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/728x90.jpg\" alt=\"Add Comma in Excel between Names with SUBSTITUTE Formula\" width=\"728\" height=\"90\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/728x90.jpg 728w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/728x90-300x37.jpg 300w\" sizes=\"(max-width: 728px) 100vw, 728px\" \/><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&nbsp; 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. To start off, we will do\u00a0a simple example of merging the first [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":17423,"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":[216,117,217],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/5646"}],"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=5646"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/5646\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17423"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=5646"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=5646"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=5646"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}