{"id":3614,"date":"2021-09-27T17:13:46","date_gmt":"2021-09-28T00:13:46","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=3614"},"modified":"2021-09-27T17:13:48","modified_gmt":"2021-09-28T00:13:48","slug":"how-to-merge-columns-to-create-a-new-column-in-power-query-power-bi-and-excel","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=3614","title":{"rendered":"How to Merge Columns to Create a New Column in Power Query (Power BI and Excel)"},"content":{"rendered":"\n

Merge columns (Power Query)<\/h3>\n\n\n\n

Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010<\/p>\n\n\n\n

In Power Query, you can merge two or more columns in your query. You can merge columns to replace them with one merged column, or create a new merged column alongside the columns that are merged. You can only merge columns of a Text <\/strong>data type. The following data is used in the examples.<\/p>\n\n\n\n

Tip <\/strong>   It’s a good idea to create a new column and keep the original columns so you can better prevent refresh errors that might happen down the road.<\/p>\n\n\n\n

\"Sample<\/figure>\n\n\n\n

Merge columns to replace existing columns<\/h2>\n\n\n\n

Merge columns to create a new column<\/h2>\n\n\n\n

You can insert a custom column into the query table and use a custom column to effectively merge two or more columns. In this case, the merged columns are still available along with the new merged column in the query table.<\/p>\n\n\n\n

In this example, we are merging OrderID <\/strong>and CustomerID <\/strong>separated by a space character.<\/p>\n\n\n\n

  1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query <\/strong>> Edit<\/strong>. For more information see Create, load, or edit a query in Excel<\/a>.<\/li>
  2. Ensure that the columns that you want to merge are of Text<\/strong> data type. Select Transform <\/strong>> Change Type<\/strong> > Text<\/strong>.<\/li>
  3. Select  Add Column <\/strong>>  Custom Column<\/strong>. The Custom Column<\/strong> dialog box appears.<\/li>
  4. In the Available Columns<\/strong> list, select the first column, and then select Insert<\/strong>. You can also double-click the first column. The column is added to the Custom Column Formula<\/strong> box immediately after the equal sign (=).

    Tip <\/strong>   In the Custom Column Formula<\/strong> box, press Ctrl + Space to display columns and other formula elements.<\/li>
  5. In the Custom Column Formula<\/strong> box, enter an ampersand (&) after the first column that you inserted.

    The ampersand (&) operator is used to combine values in Power Query in the same way as in Excel.<\/li>
  6. You can specify an additional separator, for example a space character, by entering ” ” after the ampersand (&).<\/li>
  7. In the Custom Column Formula<\/strong> box, enter another ampersand (&) after the space character.<\/li>
  8. In the Available Columns<\/strong> list, select the second column, and then select Insert<\/strong>. You can also double-click the second column. 

    \"Specify<\/li>
  9. Select OK. <\/strong><\/li><\/ol>\n\n\n\n

    Result<\/strong>

    \"Custom<\/p>\n\n\n\n

    You can rename the custom column so that it is more meaningful to you. For more information, see Rename a column.<\/a><\/p>\n\n\n\n

    Ref: https:\/\/support.microsoft.com\/en-us\/office\/merge-columns-power-query-80ec9e1e-1eb6-4048-b500-d5d42d9f0a8d<\/p>\n","protected":false},"excerpt":{"rendered":"

    Merge columns (Power Query) Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 In Power Query, you can merge two or more columns in your query. You can merge columns to replace them with one merged column, or create a new merged column alongside the columns that are merged. You can only merge columns of a Text data Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,18,621],"tags":[874,873],"class_list":["post-3614","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-microsoft-office","category-power-bi","tag-merge-columns-in-power-bi","tag-merge-columns-in-power-query"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3614","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3614"}],"version-history":[{"count":2,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3614\/revisions"}],"predecessor-version":[{"id":3620,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3614\/revisions\/3620"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3614"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3614"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3614"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}