Forum Discussion

Keith_Walworth's avatar
Keith_Walworth
Copper Contributor
May 12, 2022

Splitting multiple choice data into separate columns

I have a big data set and one of the columns contains data that was populated from a multiple choice answer on a form where the data came from. The column in question looks something like below. I wa...
BI & Data Analysis
excel
HansVogelaar's avatar
HansVogelaar
MVP
May 12, 2022

Keith_Walworth 

Let's say Referrals is in B1 and the headings Acupuncture, Chiropractor etc. in C1, D1, ...

Enter the following formula in C2:

=IF(ISNUMBER(SEARCH(C$1,$B2)),"x","")

Fill to the right, then down (or vice versa).

See attached sample workbook.

Keith_Walworth's avatar
Keith_Walworth
Copper Contributor
May 13, 2022
Thanks Hans. I created a similar solution using a slightly different formula, by what I'm really trying to figure out is whether I can get around creating the columns manually and creating them automatically and then populating them via something like power query.
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    May 14, 2022

    Keith_Walworth Please find two possible PQ solutions in the attached workbook.

    One is not dynamic, as it will split the referrals at every comma based on what it found when I created the script. And it hard-codes the new column names. If the number of items on any of the rows increases next time, the extra items will be ignored and you would have to manually adjust the script. But most of the script was created by just clicking in the user interface (except for the one step where you add an "X", which requires some typing). And it's not very complicated to fix the step that makes the split.

     

    The second solution is dynamic. I finds out how many items are included in each row, finds the highest number and splits the column into the exact number of columns needed. But, it requires some manipulation of the underlying M-code.

     

     

    • dofrancis3's avatar
      dofrancis3
      Copper Contributor
      Sep 28, 2024

      Dear Riny_van_Eekelen 

      I reach out to you to request your support !

      As per my Excel sheet attached, could you help me with a formula that can allow me to splitting multiple choice in multiple colomns.

      I can get result only for one colomn but i would like to get results for 2 or 3 colomns.

      Thank you for your support.

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Sep 28, 2024

        dofrancis3 

        That will be much better if you start new discussion with that question rather than continue quite old thread.

        In brief, you may use =ISNUMBER(SEARCH(...)) + ISNUMBER)SEARCH(...))

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      May 14, 2022

      Riny_van_Eekelen , Keith_Walworth 

      One more variant, bit more coding

      Script is

       

      let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TextToList = Table.AddColumn(
        Source,
        "Custom",
        each List.Transform(Text.Split([Referrals], ","), Text.Trim) ),
    a = List.Sort( List.Distinct( List.Combine( TextToList[Custom]) ) ),
    IfContains = Table.AddColumn(TextToList,
        "Flags",
        each [
                b =[Custom],
                c=List.Transform(a, each if List.Contains(b, _) then "x" else "" )
             ][c] ),
    toTable = Table.FromColumns( List.Zip( IfContains[Flags] ), a)
in
    toTable

       

Resources