Transpose Multiple values column with unique valued column

[JosephKim]

Hello,

I have a list of users column with a list of assigned projects column as shown below:

How can I convert it to have a list of project and a list of assigned names as shown below:

I could split the project by "," delimiter, but I don't know how to pivot/ transpose the table to generate a unique project list and have the assigned names to each project..

Any guidance would be deeply appreciated. 

Thank you so much,

Joseph

[mahoneypat · Accepted solution]

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below. Note that it may be better to leave the data split out into rows and generate your list of names in a measure with CONCATENATEX().

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8spPVdJRctRxUorVAfEy8oBcJx1nMNc3sagSyHXWcVGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Project = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Project", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Project2", each Text.Split([Project], ",")),
    #"Expanded Project2" = Table.ExpandListColumn(#"Added Custom", "Project2"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Project2",{"Project"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Project2"}, {{"People", each Text.Combine([Name], ", "), type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Project2", "Project"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Project", type text}})
in
    #"Changed Type1"

Pat

[JosephKim]

Thank you so mucn, @mahoneypat At first, I troubled with applying the script, but eventually I was able to edit it to work it out as you have guided. One question would be how to transform to unique value column for the project list?  I really appreciate your help!