Split text column in PowerBI

Question

Hi all, I have a column of text like the one in the picture, how can I split this type of column in multiple column for each occurrence of " |-Starting " substring?

Answer 1

• create a "grouper" column to group the different sets of rows
• then Group
  • Split each subgroup into columns
  • Transpose the results

eg

let
    Source = Excel.CurrentWorkbook(){[Name="Table16"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),

//create a "grouper" column
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "grouper", each if [Column1] = "|-Starting" then [Index] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"grouper"}),

//group the rows creating a delimiter separated string
//  and a counter to obtain the number of columns for the "Split"
    #"Grouped Rows" = Table.Group(#"Filled Down", {"grouper"}, {
        {"group", each Text.Combine([Column1],";"),type text},
        {"numInGroup", each Table.RowCount(_)}
        }),

//maximum number of columns in the result
    numCols=List.Max(#"Grouped Rows"[numInGroup]),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"grouper","numInGroup"}),

//split; then transpose
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "group", 
        Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv),numCols),
    #"Transposed Table" = Table.Transpose(#"Split Column by Delimiter")
in
    #"Transposed Table"