Forum Discussion
Split column by comma then pivot
Hi all,
I have a dataset that has multiple values in one column, in order, seprated by a comma. The data looks like this:
| ID | Market Area | Contract # | close date | Solution | OPP | OPP2 |
| 1 | East Coast | 111 | 2020-11-04,2020-11-20,2021-05-05 | Online,B2B, Mail | Web,Social,Media | Social |
I am trying to accomplish this:
| ID | Market Area | Contract # | close date | Solution | OPP | OPP2 |
| 1 | East Coast | 111 | 11/4/2020 | Online | Web | Social |
| 1 | East Coast | 111 | 11/20/2020 | B2B | Social | |
| 1 | East Coast | 111 | 5/5/2021 | Media |
I tired to split by comma then unpoivot the columns but it seems to repete the wrong values in the unvoited column. Any ideas on how to do this correctly?
Thank you!
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJNLC5RcM4HkUCeoSFIzMjAyEDX0FDXwEQHxjQyADGBQqZABFTin5eTmZeq42TkpKPgm5iZAxQKT03SCc5PzkzM0fFNTclMBApBuEqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Market Area" = _t, #"Contract #" = _t, #"close date" = _t, Solution = _t, OPP = _t, OPP2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Market Area", type text}, {"Contract #", Int64.Type}, {"close date", type text}, {"Solution", type text}, {"OPP", type text}, {"OPP2", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([close date], ",")), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Split([Solution], ",")), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each Text.Split([OPP], ",")), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each Text.Split([OPP2], ",")), #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"close date", "Solution", "OPP", "OPP2"}), #"Added Custom4" = Table.AddColumn(#"Removed Columns", "Custom.4", each Table.FromColumns({[Custom], [Custom.1], [Custom.2], [Custom.3]})), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom4",{"Custom", "Custom.1", "Custom.2", "Custom.3"}), #"Expanded Custom.4" = Table.ExpandTableColumn(#"Removed Columns1", "Custom.4", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"}) in #"Expanded Custom.4"Source:
Final output:
I Learned from this video and want to give credit: https://www.youtube.com/watch?v=JMOnr3DOqyk
3 Replies
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJNLC5RcM4HkUCeoSFIzMjAyEDX0FDXwEQHxjQyADGBQqZABFTin5eTmZeq42TkpKPgm5iZAxQKT03SCc5PzkzM0fFNTclMBApBuEqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Market Area" = _t, #"Contract #" = _t, #"close date" = _t, Solution = _t, OPP = _t, OPP2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Market Area", type text}, {"Contract #", Int64.Type}, {"close date", type text}, {"Solution", type text}, {"OPP", type text}, {"OPP2", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([close date], ",")), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Split([Solution], ",")), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each Text.Split([OPP], ",")), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each Text.Split([OPP2], ",")), #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"close date", "Solution", "OPP", "OPP2"}), #"Added Custom4" = Table.AddColumn(#"Removed Columns", "Custom.4", each Table.FromColumns({[Custom], [Custom.1], [Custom.2], [Custom.3]})), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom4",{"Custom", "Custom.1", "Custom.2", "Custom.3"}), #"Expanded Custom.4" = Table.ExpandTableColumn(#"Removed Columns1", "Custom.4", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"}) in #"Expanded Custom.4"Source:
Final output:
I Learned from this video and want to give credit: https://www.youtube.com/watch?v=JMOnr3DOqyk
You welcome
FYI, I had this requirement a while back and remembered the video and used for your needs. I want to give credit the video person, hence included the link