{"id":4014,"date":"2022-02-15T00:18:25","date_gmt":"2022-02-15T08:18:25","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=4014"},"modified":"2022-02-15T00:18:27","modified_gmt":"2022-02-15T08:18:27","slug":"how-to-split-a-column-with-multiple-values-into-rows-excel","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=4014","title":{"rendered":"How to Split a Column with Multiple Values into Rows (Excel)"},"content":{"rendered":"\n

I have received some sales data from John, my favourite imaginary salesperson.  He\u2019s decided to merge some of his data.<\/p>\n\n\n\n

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

I have a list of companies on each line, instead of one row for each company.  I want to have one row per company so that I can link to other company data.<\/p>\n\n\n\n

I start by extracting my data to Power Query using \u2018From Table\u2019 on the \u2018Get & Transform\u2019 section of the \u2018Data\u2019 tab:<\/p>\n\n\n\n

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

I keep the headings and create my table.<\/p>\n\n\n\n

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

I want to split the column Company<\/em><\/strong>.  I select this column and right-click to see the options:<\/p>\n\n\n\n

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

I can split my column \u2018By Delimiter\u2026\u2019, so I choose this option.<\/p>\n\n\n\n

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

I can choose to split by semicolon (;) \u2013 however, I don\u2019t want to split into multiple columns, as each separate piece of data will be a company, so I look at the \u2018Advanced options\u2019 available.<\/p>\n\n\n\n

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

I can split into rows, so I choose this option instead.<\/p>\n\n\n\n

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

I now have a row for each company, with one simple step (and no M<\/strong> code knowledge required!).  The generated M<\/strong> code is:<\/p>\n\n\n\n

= Table.ExpandListColumn(Table.TransformColumns(#”Changed Type”, {{“Company”, Splitter.SplitTextByDelimiter(“;”, QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), “Company”)<\/strong><\/p>\n\n\n\n

This has used the M<\/strong> function Table.ExpandListColumn()<\/strong>:<\/p>\n\n\n\n

Table.ExpandListColumn(table<\/strong> as table, column<\/strong> as text) as table <\/p>\n\n\n\n

Given a column of list data in a table, this creates a copy of a row for each value in its list.<\/p>\n\n\n\n

Power Query has converted the Company<\/em><\/strong> column to a column of lists by using Splitter.SplitTextByDelimiter()<\/strong>, and then converted that list into rows.  In this case, my data was delimited by a simple semicolon, but there are also options to use special characters, making this a very powerful function when dealing with complex columns. <\/p>\n\n\n\n

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

Ref: https:\/\/www.sumproduct.com\/blog\/article\/power-query-blogs\/power-query-rows-of-columns<\/p>\n","protected":false},"excerpt":{"rendered":"

I have received some sales data from John, my favourite imaginary salesperson.  He\u2019s decided to merge some of his data. I have a list of companies on each line, instead of one row for each company.  I want to have one row per company so that I can link to other company data. I start 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":[1113,1112],"class_list":["post-4014","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-microsoft-office","category-power-bi","tag-split-a-column-into-rows","tag-split-a-column-with-multiple-values-into-rows"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/4014","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=4014"}],"version-history":[{"count":1,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/4014\/revisions"}],"predecessor-version":[{"id":4015,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/4014\/revisions\/4015"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4014"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4014"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4014"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}