Clean survey data in Power Query: multi-select answers to rows
How-to · By Tahir Nas · Examples run in our simulator · Updated
9 min read
Multi-select survey answers arrive as one cell per respondent with the chosen options joined by a delimiter, such as Email;Phone;Live chat. To analyse them in Power Query, split each cell into a list, tidy the items, and expand the list so each respondent gets one row per option; then counts, percentages and cross-tabs become ordinary group and pivot steps.
Why multi-select answers need reshaping
Survey tools export a "tick all that apply" question in one of two shapes. Some give one column per option with a tick or a 1. Others give a single column where the selected options are joined with a semicolon or comma. The second shape looks compact, but nothing useful can be done with it as it stands: you can't count how many people chose "Phone" by grouping on the column, because Email;Phone and Phone are different values.
The fix is to reshape to a long table: one row per respondent per selected option. Microsoft's article on splitting columns by delimiter shows the ribbon route (Split Column > By Delimiter, with Split into: Rows under the advanced options). The M below does the same thing with the cleaning built in, which matters because survey text is rarely tidy.
Split each answer into one row per option
This sample has the usual problems: inconsistent spacing after the semicolon, a lower-case option, a trailing delimiter and a respondent who skipped the question.
let
Responses = #table(type table [Respondent = text, Channels = nullable text],
{{"R1", "Email;Phone"}, {"R2", "email; Live chat;"}, {"R3", null}, {"R4", "Phone"}, {"R5", "Email;Phone;Live chat"}}),
AsLists = Table.TransformColumns(Responses, {{"Channels", each
if _ = null then {} else List.Select(List.Transform(Text.Split(_, ";"), each Text.Proper(Text.Trim(_))), each _ <> ""), type list}}),
Rows = Table.ExpandListColumn(AsLists, "Channels")
in
RowsResult
| Respondent | Channels |
|---|---|
| R1 | |
| R1 | Phone |
| R2 | |
| R2 | Live Chat |
| R3 | null |
| R4 | Phone |
| R5 | |
| R5 | Phone |
| R5 | Live Chat |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The result has nine rows. R1 has two (Email and Phone), R2 has two (Email and Live Chat), R4 has one, R5 has three, and R3 has a single row with null.
Reading the transform from the inside out:
- Text.Split turns
"email; Live chat;"into{"email", " Live chat", ""}. - Text.Trim removes the stray space and
Text.Propermakes the case consistent, soemailandEmailbecome the same option (Power Query treats them as different values otherwise, as case sensitivity in Power Query explains). List.Select(…, each _ <> "")drops the empty item left by the trailing semicolon. Without it, R2 would get a blank row.- A
nullanswer becomes an empty list{}instead of an error, becauseText.Splitneeds text.
Table.ExpandListColumn then gives each list item its own row and copies the other columns. Notice what it does with R3's empty list: it keeps the respondent with a single null row rather than dropping them. That's useful for a respondent-level table, but it would count as an answer if you counted rows, so the next step filters skipped answers out before counting.
Text.Proper is a blunt tool: it gives "Live Chat", not "Live chat". For short option labels that's usually fine. If labels must match the questionnaire exactly, map them with a table instead (covered below). The lesson on splitting columns and extracting text and the lesson on list columns go deeper into both steps.
Count respondents per option, with the right denominator
The question people ask of multi-select data is "what share of respondents chose each option?" Two details decide whether the answer is right: count distinct respondents (not rows), and pick the denominator deliberately.
let
Responses = #table(type table [Respondent = text, Channels = nullable text],
{{"R1", "Email;Phone"}, {"R2", "email; Live chat;"}, {"R3", null}, {"R4", "Phone"}, {"R5", "Email;Phone;Live chat"}}),
AsLists = Table.TransformColumns(Responses, {{"Channels", each
if _ = null then {} else List.Select(List.Transform(Text.Split(_, ";"), each Text.Proper(Text.Trim(_))), each _ <> ""), type list}}),
Answered = Table.RowCount(Table.SelectRows(AsLists, each not List.IsEmpty([Channels]))),
Rows = Table.ExpandListColumn(Table.SelectRows(AsLists, each not List.IsEmpty([Channels])), "Channels"),
Counts = Table.Group(Rows, {"Channels"}, {{"Respondents", each List.Count(List.Distinct([Respondent])), Int64.Type}}),
Share = Table.AddColumn(Counts, "% of respondents", each Number.Round([Respondents] / Answered, 2), type number),
Sorted = Table.Sort(Share, {{"Respondents", Order.Descending}, {"Channels", Order.Ascending}})
in
SortedResult
| Channels | Respondents | % of respondents |
|---|---|---|
| 3 | 0.75 | |
| Phone | 3 | 0.75 |
| Live Chat | 2 | 0.5 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Email and Phone were each chosen by 3 of the 4 people who answered (0.75), and Live Chat by 2 (0.5). The shares add up to 2.0, not 1.0, because each person could pick more than one option. That's correct for a multi-select question, and it's the first thing to explain to anyone reading the chart.
The denominator here is 4: respondents who answered the question. R3 skipped it, so including R3 would make every option look less popular. Some reports use all survey respondents instead, which answers a slightly different question ("what share of everyone surveyed uses email?"). Either is defensible; state which one the report uses.
Counting with List.Distinct([Respondent]) rather than Table.RowCount protects against a respondent who ticked the same option twice through a data-entry quirk, such as Email;email, which becomes two "Email" rows after cleaning. The List.Distinct reference shows how it treats repeated values.
Map spelling variants to the options you expect
Free-text options and older survey versions produce variants: "E-mail" and "Email", "Telephone" and "Phone". Trimming and case changes don't fix those. A mapping table does, and it also exposes anything new:
let
Rows = #table(type table [Respondent = text, Channels = text],
{{"R1", "Email"}, {"R2", "E-mail"}, {"R3", "Phone"}, {"R4", "Telephone"}, {"R5", "Fax"}}),
Map = #table(type table [Raw = text, Option = text], {{"Email", "Email"}, {"E-mail", "Email"}, {"Phone", "Phone"}, {"Telephone", "Phone"}}),
Joined = Table.NestedJoin(Rows, {"Channels"}, Map, {"Raw"}, "M", JoinKind.LeftOuter),
Expanded = Table.ExpandTableColumn(Joined, "M", {"Option"}),
Unmapped = Table.SelectRows(Expanded, each [Option] = null)
in
UnmappedResult
| Respondent | Channels | Option |
|---|---|---|
| R5 | Fax | null |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The query returns one row: R5's "Fax", which the mapping table doesn't know. In the full query you'd keep the Option column for analysis and use this unmapped list as a check, either a small table that someone reviews after each refresh or an error that stops the refresh. The guide to mapping tables instead of nested if explains why a table beats a chain of Text.Replace steps.
When an option contains the delimiter
Splitting on a delimiter fails when an option label contains it. A question with the option "Yes, sometimes" exported with comma separators gives Yes, sometimes, Email, and a comma split produces "Yes", "sometimes" and "Email". The same happens with an "Other (please specify)" box, where people type whatever they like.
When the option list is fixed, test for each known option instead of splitting:
let
Responses = #table(type table [Respondent = text, Channels = text],
{{"R1", "Email, Phone"}, {"R2", "Yes, sometimes, Email"}, {"R3", "Other: WhatsApp"}}),
Known = {"Email", "Phone", "Live chat", "Yes, sometimes"},
Matched = Table.AddColumn(Responses, "Answers", each Text.Combine(List.Select(Known, (k) => Text.Contains([Channels], k)), " | "), type text),
Other = Table.AddColumn(Matched, "Other text", each if Text.Contains([Channels], "Other: ") then Text.AfterDelimiter([Channels], "Other: ") else null, type nullable text)
in
OtherResult
| Respondent | Channels | Answers | Other text |
|---|---|---|---|
| R1 | Email, Phone | Email | Phone | null |
| R2 | Yes, sometimes, Email | Email | Yes, sometimes | null |
| R3 | Other: WhatsApp |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
R1 matches "Email | Phone", R2 matches "Email | Yes, sometimes", and R3 matches nothing from the fixed list but gets "WhatsApp" in the Other text column. The answers are joined with " | " here only so they're readable; in a real query keep them as a list and expand to rows as before.
Matching by Text.Contains has its own trap: an option that is part of another (such as "Phone" and "Phone banking") matches both. Order the checks or match whole items when labels overlap. The better fix, when you control the survey, is to export with a delimiter that can't appear in a label, or to export one column per option.
Pivot back to one column per option
Some analysis needs the wide shape: one row per respondent with a true/false column for each option, for cross-tabs ("of people who use email, how many also use phone?") or a model. Pivot the long table with a flag column, then replace the gaps with false:
let
Rows = #table(type table [Respondent = text, Channels = text],
{{"R1", "Email"}, {"R1", "Phone"}, {"R2", "Email"}, {"R2", "Live Chat"}, {"R4", "Phone"}, {"R5", "Email"}, {"R5", "Phone"}, {"R5", "Live Chat"}}),
Flag = Table.AddColumn(Rows, "Ticked", each true, type logical),
Wide = Table.Pivot(Flag, List.Sort(List.Distinct(Flag[Channels])), "Channels", "Ticked"),
Filled = Table.ReplaceValue(Wide, null, false, Replacer.ReplaceValue, List.Skip(Table.ColumnNames(Wide), 1))
in
FilledResult
| Respondent | Live Chat | Phone | |
|---|---|---|---|
| R1 | true | false | true |
| R2 | true | true | false |
| R4 | false | false | true |
| R5 | true | true | true |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The result has four respondents and three option columns (Email, Live Chat, Phone). R5 is true in all three; R4 is true only for Phone. Table.Pivot leaves null where a respondent didn't choose an option, so the last step turns those into false. Taking the column list from the data (List.Distinct(Flag[Channels])) means a new option becomes a new column on the next refresh without editing the query.
R3, who skipped the question, isn't in this table because the long table had no rows for them. If the wide table must include every respondent, merge it onto the full respondent list and treat missing rows as "not answered", which is different from "answered, but didn't tick this option".
A cleaning order that holds up
Survey exports change between waves, so build the query in an order that keeps each problem in one place:
| Step | What it handles |
|---|---|
| Split to a list | Delimited answers |
| Trim, standardise case, drop empty items | Spacing, case, trailing delimiters |
| Map raw text to options | Spelling variants, renamed options |
| Check for unmapped values | New options, typos, "Other" text |
| Expand to rows | One row per respondent per option |
| Group or pivot | Counts, shares, cross-tabs |
Column names from survey tools are often long question texts that change between waves. The post on handling changing column headers covers renamed columns more generally. The exercise Clean every column name, including future ones practises renaming them with a rule rather than by hand.
Practise the steps
The central move is splitting a delimited cell into rows. Try it in the exercise Split a tag list into one row per tag, then Count answered questions using the whole row, which deals with skipped answers in survey data. The guide to split column recipes collects other delimiter patterns, and the survey responses pivot download gives you a practice file to reshape. Power Query Exercises runs each answer against test data in the browser, so a query that only handles tidy sample rows gets caught.
Frequently asked questions
Should I split into rows or into columns?
Rows, for analysis. Splitting into columns gives "Channels.1", "Channels.2" and so on, where Email may sit in any of them depending on the order people ticked. Counting is then awkward and every new maximum number of choices adds a column. Columns are the right shape for a different job, such as splitting full names into first and last names.
How do I keep the order in which options were selected?
Add an index to each list before expanding, for example with List.Positions or by pairing items with {0..List.Count(_) - 1}. Survey tools differ here: some export options in questionnaire order rather than the order they were ticked, so check before relying on it.
Can the same approach handle ranking questions?
Partly. A ranking question exported as Phone;Email;Chat (in rank order) splits the same way, and the position in the list is the rank. Keep the position as a column before expanding so the rank isn't lost.
Sources
- splitting columns by delimiter (learn.microsoft.com)
- Text.Split (learn.microsoft.com)
- Table.ExpandListColumn (learn.microsoft.com)
- Table.Pivot (learn.microsoft.com)
About the author
Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.
Keep reading
- How to split full names into first and last names in Power QuerySplit a full name column into first and last names in Power Query, with rules for middle names, single names, 'Last, First' order, titles and suffixes.
- Cleaning phone numbers and postcodes with Power QueryStandardise UK phone numbers and postcodes in Power Query: keep only digits, fix +44 and lost leading zeros, re-space postcodes and flag values that fail.
- Fuzzy matching in Power Query: standardise inconsistent namesStandardise inconsistent product names in Power Query: normalise text first, let fuzzy merge suggest matches, then keep checked matches in a mapping table.
Practise Power Query in the browser with graded exercises checked against fresh data.
Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.