Latest Enterprise DNA Initiatives

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

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,

1 Like

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)

3 Likes

Hi @Ikay,

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

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)

1 Like

Thank you so much guys!, @Melissa @alexbadiu

1 Like

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 :slight_smile:

Thanks

1 Like

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. :+1:

The core of the solution is the GetStrings step.

1 Like

Ah!, great, thanks

1 Like