split to distinct columns

I copied your data from the spreadsheet and put it in through the Enter Data approach. The query is below (and contains your data). You can create a blank query, open the Advanced Editor and then replace the text there with this.

The last two steps are customized. The #"Grouped Rows" keeps the Name column as a List (usually Group steps result in a table). The Custom1 step uses a function not used often called Table.FromColumns. It converts a list of lists into a table, and the 2nd parameter provides the column names as a list.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY5JDoNADAT/4nMfGJgAOYY1YckHRpz7/z8AW05G4lgld8kpyUsgDGAJVnIgSafiomjUK1XgA6zBxtygLl4EtuATDIX50VOOk09bozmP8uKd6+rNfbxyry/qM65+Fv9nwfzm79+q+69q9PWj2r47Tg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Groups = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Groups", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Groups], ",")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Groups"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
    #"Grouped Rows" = Table.Group(#"Expanded Custom", {"Custom"}, {{"All", each _[Name]}}),
    Custom1 = Table.FromColumns(#"Grouped Rows"[All], #"Grouped Rows"[Custom])
in
    Custom1

I am still curious what analyses/visuals you have planned. This data structure is likely not ideal.

Pat