Search code examples
excelpowerquery

How can I split one row into rows in power query?

I am using 365 Microsoft office. I need to split row into rows in power query. I have a table contains dataenter image description here

but I want to seperate it into rows by 1., 2., 3., ... to get the final result like this enter image description here

now I used text.split and then table.fromcolumns but it does not work because I used line feed to split the row (#(lf)) and I got the table like below, but when I put Text.Split([#"Spare"],{"0..9",".",0,Relativeposition.fromstart})...I got an error result. Please kindly help. enter image description here

Solution

  • This should get you to the second image:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SpitToTable = Table.AddColumn(Source, "Rows", each Table.FromColumns({
        Splitter.SplitTextByCharacterTransition({"#(lf)"}, {"0".."9"})([Spare]),
        Splitter.SplitTextByCharacterTransition({"#(lf)"}, {"0".."9"})([int]),
        Splitter.SplitTextByCharacterTransition({"#(lf)"}, {"0".."9"})([com])
        },
        {"Spare","int","com"})),
    #"Removed Other Columns" = Table.SelectColumns(SpitToTable,{"Rows"}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Removed Other Columns", "Rows", {"Spare", "int", "com"}, {"Spare", "int", "com"}),
    #"Trimmed Text" = Table.TransformColumns(#"Expanded Rows",{{"Spare", Text.Trim, type text}, {"int", Text.Trim, type text}, {"com", Text.Trim, type text}})
in
    #"Trimmed Text"