Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
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.
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
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
Check out the July 2025 Power BI update to learn about new features.