Splitting Multiple Cells consecutive columns into separate rows

[LT_Maverick]

I am not sure if Power Query or if there is a more tidious cleaning of data in Excel that I should be doing. The last four (4) columns in my spreadsheet, I want to take the values and split them into their own rows, but with the data from the preceding columns and the same "line" of data from the other columns following. My goal is to validate Antibiotics administration prior to the "Surgical Incision Time".  

Columns "SCIP Antibiotic Name", "Antibiotic Date", "Antibiotic Time", and "Antibiotic Route" all have the same value of data. If Column "H" has three (3) values, then so will column "I", "J" and "K". All the values in the columns are preceded by 1. , 2. , 3. , 4. , etc. Sometimes they're dates in "Antibiotic Date" that do not match the "Surgical Start Date" so I will want to filter those out. Which is Why I can't just split and delete to reduce the number of columns. As I will also want to calculate averages based on the type of "Antibiotic" that was used. I have tried multiple ways of using Power Query to help me split the columns and align the data in new rows, but I have not been successful. 

CaseID	Principal Procedure	Surgery Start Date	Surgical Incision Time	Surgery End Time	Anesthesia Start Time	Anesthesia End Time	SCIP Antibiotic Name	Antibiotic Date	Antibiotic Time	Antibiotic Route
9097474	Arthroplasty Hip Hemi Anterior Approach	5/13/2021	2:29 PM	3:54 PM	1:52 PM	3:59 PM	1. Amoxicillin 250 Mg Or Caps [535] 2. Amoxicillin 250 Mg Or Caps [535] 3. Cefazolin Sodium 2 Gm/20ml Iv Sosy [168359] 4. Cefazolin Sodium 2 Gm/20ml Iv Sosy [168359] 5. Cefazolin Sodium 2 Gm/20ml Iv Sosy [168359]	1. 5/13/2021 2. 5/14/2021 3. 5/13/2021 4. 5/13/2021 5. 5/14/2021	1. 2046 2. 2039 3. 1404 4. 2047 5. 0518	1. Oral 2. Oral 3. Intravenous 4. Intravenous 5. Intravenous
994009	Subtotal Colectomy, Ostomy	5/6/2021	1:47 PM	6:25 PM	1:21 PM	6:25 PM	1. Amoxicillin 500 Mg Or Caps [536] 2. Amoxicillin 500 Mg Or Caps [536] 3. Clarithromycin 500 Mg Or Tabs [11368] 4. Clarithromycin 500 Mg Or Tabs [11368] 5. Piperacillin-Tazobactam 3.375 G (Mini-Bag Plus) [410082] 6. Piperacillin-Tazobactam 3.375 G (Mini-Bag Plus) [410082] 7. Piperacillin-Tazobactam 3.375 G (Mini-Bag Plus) [410082] 8. Piperacillin-Tazobactam 3.375 G (Mini-Bag Plus) [410082] 9. Piperacillin-Tazobactam 3.375 G (Mini-Bag Plus) [410082] 10. Piperacillin-Tazobactam 3.375 G (Mini-Bag Plus) [410082] 11. Piperacillin-Tazobactam 3.375 G (Mini-Bag Plus) [410082] (...)	1. 4/29/2021 2. 4/30/2021 3. 4/29/2021 4. 4/30/2021 5. 4/30/2021 6. 4/30/2021 7. 5/1/2021 8. 5/1/2021 9. 5/2/2021 10. 5/2/2021 11. 5/2/2021 (...)	1. 2010 2. 0921 3. 2035 4. 0921 5. 1511 6. 2032 7. 0503 8. 1412 9. 0346 10. 1234 11. 2216 (...)	1. Oral 2. Oral 3. Oral 4. Oral 5. Intravenous 6. Intravenous 7. Intravenous 8. Intravenous 9. Intravenous 10. Intravenous 11. Intravenous (...)
9987856	Craniotomy Subdural Hematoma Evacuation	11/24/2023	9:11 AM	10:48 AM	8:10 AM	10:50 AM	1. Amoxicillin-Pot Clavulanate 875-125 Mg Or Tabs [38298] 2. Amoxicillin-Pot Clavulanate 875-125 Mg Or Tabs [38298] 3. Amoxicillin-Pot Clavulanate 875-125 Mg Or Tabs [38298] 4. Amoxicillin-Pot Clavulanate 875-125 Mg Or Tabs [38298] 5. Amoxicillin-Pot Clavulanate 875-125 Mg Or Tabs [38298] 6. Amoxicillin-Pot Clavulanate 875-125 Mg Or Tabs [38298] 7. Amoxicillin-Pot Clavulanate 875-125 Mg Or Tabs [38298] 8. Cefazolin Sodium 1 G Ij Solr [1739] 9. Vancomycin Hcl 1 G Iv Solr [175312] 10. Cefazolin 2 G (Minibag Plus) [410720] 11. Cefazolin 2 G (Minibag Plus) [410720] (...)	1. 11/22/2023 2. 11/23/2023 3. 11/23/2023 4. 11/24/2023 5. 11/25/2023 6. 11/25/2023 7. 11/26/2023 8. 11/24/2023 9. 11/24/2023 10. 11/24/2023 11. 11/25/2023 (...)	1. 2238 2. 0915 3. 2117 4. 2019 5. 0918 6. 2016 7. 0911 8. 0843 9. 0921 10. 1741 11. 0209 (...)	1. Oral 2. Oral 3. Oral 4. Oral 5. Oral 6. Oral 7. Oral 8. Intravenous 9. Undetermined 10. Intravenous 11. Intravenous (...)
9009709	Arthroplasty Hip Total	5/9/2021	1:18 PM	3:09 PM	12:39 PM	3:12 PM	1. Amoxicillin-Pot Clavulanate 875-125 Mg Or Tabs [38298] 2. Amoxicillin-Pot Clavulanate 875-125 Mg Or Tabs [38298] 3. Amoxicillin-Pot Clavulanate 875-125 Mg Or Tabs [38298] 4. Amoxicillin-Pot Clavulanate 875-125 Mg Or Tabs [38298] 5. Cefazolin Sodium 1 G Ij Solr [1739] 6. Cefazolin Sodium 2 Gm/20ml Iv Sosy [168359] 7. Cefazolin Sodium 2 Gm/20ml Iv Sosy [168359]	1. 5/9/2021 2. 5/10/2021 3. 5/11/2021 4. 5/11/2021 5. 5/9/2021 6. 5/9/2021 7. 5/10/2021	1. 2000 2. 1236 3. 0035 4. 1358 5. 1307 6. 2214 7. 0606	1. Oral 2. Oral 3. Oral 4. Oral 5. Intravenous 6. Intravenous 7. Intravenous

[AlienSx · Accepted solution]

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

[v-hashadapu]

Hi @LT_Maverick , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

[v-hashadapu]

Hi @LT_Maverick , Hope you're doing okay! May we know if it worked for you, or are you still experiencing difficulties? Let us know — your feedback can really help others in the same situation.

[v-hashadapu]

Hi @LT_Maverick , Thank you for reaching out to the Microsoft Community Forum.

We find the answer shared by @PwerQueryKees  is appropriate. Can you please confirm if the solution worked for you. It will help others with similar issues find the answer easily.
Thank you.

[PwerQueryKees · Accepted solution]

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

[AlienSx · Accepted solution]

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