Splitting Multiple Cells consecutive columns into separate rows

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    s = List.TransformMany(
        Table.ToList(Source, (x) => x),
        (x) => List.Zip(List.Transform(List.LastN(x, 4), (w) => Text.Split(w, "#(lf)"))),
        (x, y) => List.FirstN(x, 7) & y
    ),
    result = Table.FromList(s, (x) => x, Table.ColumnNames(Source))
in
    result

An alternative solution...

Starting with the table "Antibiotics":

Using this:

let
    Source = AntiBiotics,
    // Unpivot the last 4 columns to get 4 rows per row with coumn name and value as "Attibute"and 'Value'
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"CaseID", "Principal Procedure", "Surgery Start Date", "Surgical Incision Time", "Surgery End Time", "Anesthesia Start Time", "Anesthesia End Time"}, "Attribute", "Value"),
    // Split the 'Value' column into its lines.
    #"Split Column in Rows by LF" = Table.ExpandListColumn(Table.TransformColumns(#"Unpivoted Columns", {{"Value", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Value"),
    // Split the line number from each line
    #"Split Column by Space" = Table.SplitColumn(#"Split Column in Rows by LF", "Value", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Line", "Value"}),
    // Change the data type of the line number to "Whole Number" to allow sorting and to identify the line
    #"Changed Type on Line" = Table.TransformColumnTypes(#"Split Column by Space",{{"Line", Int64.Type}}),
    // Recreate the 4 data columns, but keep the line number to ensure we get a unique row for each line
    #"Pivoted Column" = Table.Pivot(#"Changed Type on Line", List.Distinct(#"Changed Type on Line"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

results in:

Did I answer your question? Then please (also) mark my post as a solution and make it easier to find for others having a similar problem.
Remember: You can mark multiple answers as a solution...
If I helped you, please click on the Thumbs Up to give Kudos.

Kees Stolker

A big fan of Power Query and Excel