create rows from a column

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