Split column by comma then pivot

[Anonymous]

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	Mail	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!

[sevenhills · Accepted solution]

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 

[Anonymous]

thank you

[sevenhills]

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