Splitting multiple choice data into separate columns

[Keith_Walworth]

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 want to go from something like this:

Referrals

Chiropractor, PCP, X-Ray

MRI, Acupuncture

Physical Therapy, MRI, Other

Injection, PCP, X-Ray

And end up with something like this:

Acupuncture	Chiropractic	Injection	MRI	Other	PCP	Physical Therapy	X-Ray
	x				x		x
x			x
			x	x		x
		x			x		x

Text to Column will break the data into separate columns, but I need a column for every possible Referral type and an x to indicate their selection.
I’m just not sure how to get it done . I’ve Googled so many of videos but I haven’t found an example that matches my situation. I can create the columns manually and through formulas populate the rows, but I was hoping to find a solution using Power Query.  Just not sure how to do it.
Any assistance you could provide would be greatly appreciated!

[#2]

@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.

[#3]

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.

[#4]

@Keith_Walworth 

I'll leave that for someone who knows PowerQuery.

[#5]

@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.

[#6]

@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