Split multiple row to multiple row by line break based on character conditions

Hey edhans ,

ha, ha, that's pretty mean.
Unfortunately I also don't have any idea what the undocumented default-splitter function is. 
Also have no idea why someone considered it not to be Splitter.SplitByNothing 😉

Hello everyone,

I have a dataframe that looks as follows:

I want to split multiple row to multiple row by line break and first character each line.

Here is the result I want:

Here is excel file for more convenient:

https://1drv.ms/x/s!AgaDgBBM4rAGli_UZM0WlmdiT7wj?e=L8WpTG

I have listed all the case that appear on table above.

Thank you!

See this code piorsenpai 

let
    Source = Excel.Workbook(File.Contents("C:\Users\Ed Hansberry\OneDrive\Downloads\test.xlsx"), null, true),
    Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data_Sheet, [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([checklist] <> null)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"num", "checklist"}, {{"All Rows", each _, type table [num=number, checklist=text, sop=text, expected=text]}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Grouped Rows", {{"checklist", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "checklist"),
    #"Filtered Rows1" = Table.SelectRows(#"Split Column by Delimiter", each ([checklist] <> "")),
    #"Added First Column" = 
        Table.AddColumn(
            #"Filtered Rows1", 
            "First Column",
            each 
                let
                    varGroupText = Text.Start([checklist], 1)
                in
                Text.Combine(
                    Table.SelectRows(
                        Table.FromList(
                            Text.Split([All Rows][sop]{0}, "#(lf)")
                        ),
                        each Text.Start([Column1],1) = varGroupText
                    )[Column1],
                "#(lf)"
                )
            ),
    #"Added Second Column" = 
        Table.AddColumn(
            #"Added First Column", 
            "Second Column",
            each 
                let
                    varGroupText = Text.Start([checklist], 1)
                in
                Text.Combine(
                    Table.SelectRows(
                        Table.FromList(
                            Text.Split([All Rows][expected]{0}, "#(lf)")
                        ),
                        each Text.Start([Column1],1) = varGroupText
                    )[Column1],
                "#(lf)"
                )
            ),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Second Column",{"num", "checklist", "First Column", "Second Column"})
in
    #"Removed Other Columns"

It turns this after I filtered out some blank/empty rows in the Excel file:

into this:

You will need to change the "Source" line to be your folder path to the test.xlsx file.