Splitting multiple comma delimited cells into multiple rows, keeping row details

[briandon1970]

Hi all,

I'm looking to write a Power Query function or Excel macro which would transform something like the first example below into something like the second - does anyone please have any suggestions?

Thanks so much.

-Brian

[v-cherch-msft · Accepted solution]

Hi @briandon1970

You may use ‘Split Column’ for the 3 columns in Query Editor. 

Regards,

Cherie

[MFelix]

Hi @briandon1970

This is not directly made in PBI you can use this post to give you the expected result.

Using you data and this post I was abble to get the expected result see the M Code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8slMTs0rzi8yVNJRCijKz0pNLjHUUYCyjICCIUBuCIgRFAxkBQUbKcXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Licensor = _t, Projects = _t, Territories = _t, RightSummary = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Licensor", type text}, {"Projects", type text}, {"Territories", type text}, {"RightSummary", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Project", each Text.Split([Projects],", ")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Territorie", each Text.Split([Territories],", ")),
    #"Added Custom3" = Table.AddColumn(#"Added Custom1", "Summary", each Text.Split([RightSummary], ", ")),
    #"Added Custom2" = Table.AddColumn(#"Added Custom3", "Custom.3", each Table.FromColumns({[Project],[Territorie],[Summary]})),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom2", "Custom.3", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.1",{"Projects", "Territories", "RightSummary", "Project", "Territorie", "Summary"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Projects"}, {"Column2", "Territories"}, {"Column3", "RightSummary"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Licensor", type text}, {"Projects", type text}, {"Territories", type text}, {"RightSummary", type text}})
in
    #"Changed Type1"

Regards,

MFelix