Forum Discussion
Transpose Multiple values column with unique valued column
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
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
2 Replies
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
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!
