Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
LT_Maverick
Frequent Visitor

Splitting Multiple Cells consecutive columns into separate rows

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. 

 

CaseIDPrincipal ProcedureSurgery Start DateSurgical Incision TimeSurgery End TimeAnesthesia Start TimeAnesthesia End TimeSCIP Antibiotic NameAntibiotic DateAntibiotic TimeAntibiotic Route
9097474Arthroplasty Hip Hemi Anterior Approach5/13/20212:29 PM3:54 PM1:52 PM3:59 PM1. 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
994009Subtotal Colectomy, Ostomy5/6/20211:47 PM6:25 PM1:21 PM6:25 PM1. 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
(...)
9987856Craniotomy Subdural Hematoma Evacuation11/24/20239:11 AM10:48 AM8:10 AM10:50 AM1. 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
(...)
9009709Arthroplasty Hip Total5/9/20211:18 PM3:09 PM12:39 PM3:12 PM1. 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
3 REPLIES 3
v-hashadapu
Community Support
Community Support

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.

PwerQueryKees
Super User
Super User

An alternative solution...

 

Starting with the table "Antibiotics":

PwerQueryKees_0-1752845296567.png

 

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:

PwerQueryKees_1-1752845455341.png



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
Super User
Super User

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.