Forum Discussion

briandon1970's avatar
briandon1970
New Member
8 years ago
Solved

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

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 sugges...
  • v-cherch-msft's avatar
    v-cherch-msft
    8 years ago

    Hi briandon1970

     

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

     

    Regards,

    Cherie

MFelix's avatar
MFelix
Super User
8 years ago

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