Count the number of occurrences of a Text in a cell of a column with multiple values

[Ikay]

Hi All,

I’m trying to analyse the results of a survey where a question says “Following the visit will you? (Please tick all that apply)”, resulting in a cell/response containing multiple options separated by a semicolon

Rather than display survey responses as is, I’m looking to breakdown each response/cell into its constituent options and count each occurrence of the options so that I can show the frequency in a bar chart. Similar to the way MS forms displays it
following the visit.xlsx (17.1 KB)

,

attached is the sample excel file,

[alexbadiu]

Hello @Ikay,

In order to achieve this you need to split columns by delimiter and by rows inside Power Query:

Filter null an empty values from the resulting column and close & apply .
You can then add a count measure and create your bar chart

Best regards,
Alex

See answer in the pbix attached
Answer.pbix (15.3 KB)

[Melissa]

Hi @Ikay,

Top notch solution by @alexbadiu leveraging the UI but I like to practice writing some M

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("5VY7DsMgDL2K1bk3yNi5W7coAyJOQHzcAhly+0I6dEwzIKCREAw8PxvsZ+j7y00gV7DS4sAbUghMM2c8GHIIk8PXgjbotXuwtMeDJAtpBIEOnPTKgxwjRE4Sx+4yXItS3hOllhP6sGoELpid0YPDmblR2jnacRKkgZP1i3km39HIsjmCiVSCfCLYmLkmj3kiLX1TpY9UCXg/9yV9l1BIso7rv0ppA++H2YqMcvlvo+P8RllS46XrI2vmv1P1NZD/F9Fepqt+pw5QHoDW/z6d3X/9GTrz3Td19pK/50ONaXgD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Following the visit will you? (Please tick all that apply)" = _t]), 
  NoBlanks = Table.SelectRows( Source, each ([#"Following the visit will you? (Please tick all that apply)"] <> "")), 
  GetStrings = 
    Table.ExpandListColumn(
      Table.TransformColumns(NoBlanks, 
      {{
        "Following the visit will you? (Please tick all that apply)", 
          each 
            let
              fxSplit = Splitter.SplitTextByAnyDelimiter({";"})
            in
              fxSplit(Text.TrimEnd(_, ";"))
      }}
    ), "Following the visit will you? (Please tick all that apply)"
  )
in
  GetStrings

.
Will get you the same result of course

Here’s my sample:
eDNA - forms delimited text count.pbix (16.2 KB)

[Ikay]

Thank you so much guys!, @Melissa @alexbadiu

[Ikay]

Hi @Melissa , please where is this long string after Binary.FromText coming from … (Binary.Decompress(Binary.FromText("5VY7DsMgDL2K1bk3yNi5W7coAyJOQHzcAhly+…

there are a few other columns I’d like to replicate this for

Thanks

[Melissa]

Hi @Ikay,

The Source string was automatically generated when I copied your sample into the “Enter Data/Create table” window. You can do that if you have less than 3.000 values across both columns and rows. That translates everything into binary so you don’t have to connect to an external source file.

The core of the solution is the GetStrings step.

[Ikay]

Ah!, great, thanks