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 ...
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
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
thank you
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