create rows from a column

[hogehoge]

Let's say I have this:

1, Bob;Mike;Paul, 2, 3

I need to split them into multiple rows

1, Bob, 2, 3

1, Mike, 2, 3

1, Paul, 2, 3

I've tried creating a custom column with something like

=Table.AddColumn(Source,"Custom", Table.FromColumns({Text.Split([Column2], ";")})) but doesn't seem to work 😞 

Can anyone help this newbi? 

[Ashish_Mathur]

Hi,

Try this M code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Changed Type","1,","1^",Replacer.ReplaceText,{"Data"}),
    #"Replaced Value" = Table.ReplaceValue(#"Replaced Value1",";",";1^",Replacer.ReplaceText,{"Data"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Replaced Value", {{"Data", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Data"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Data", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Data", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), {"Data.1", "Data.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Data.1", type text}, {"Data.2", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type2",{{"Data.2", Text.Trim}}),
    #"Filled Up" = Table.FillUp(#"Trimmed Text",{"Data.2"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Filled Up","^",",",Replacer.ReplaceText,{"Data.1"}),
    #"Merged Columns" = Table.CombineColumns(#"Replaced Value2",{"Data.1", "Data.2"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

[MariaP · Accepted solution]

Hi, 

Here are the steps I used in Edit Query, although there is probably a quicker way this produces the resutls you have requested.

let
    Source = Excel.Workbook(File.Contents(FilePath &"SplitName.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"value1", Int64.Type}, {"value2Names", type text}, {"value3", Int64.Type}, {"value4", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"value3", type text}, {"value4", type text}}, "en-AU"),{"value3", "value4"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Merged Columns", "value2Names", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"value2Names.1", "value2Names.2", "value2Names.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"value2Names.1", type text}, {"value2Names.2", type text}, {"value2Names.3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"value2Names.1", "value2Names.2", "value2Names.3", "Merged"}, "Attribute", "Value"),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"Merged", "Attribute", "Value"}, "Attribute.1", "Value.1"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Unpivoted Columns1", "Merged", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Merged.1", "Merged.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Attribute", "Attribute.1"})
in
    #"Removed Columns"

Excel File Sample: Excel SpltName

File to dowonload DOWNLOAD  

Replace parameter FilePath with the location you save the excel file to on your desktop.

Result:

This should work...