Split Column Help Needed

Hi scoutmedic ,

How about this solution in Power Query?

Before:

After:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVDLDoIwEPyVTU82wfLQk3f15CPBC7EcKlRthMXwiOjX2wImhIgHN5l2NzO7ne3xSLbyAUGW34hFXNu1Pcebm9SAwkrlRalPjBVeGCxrkd4TWYDCKKliueAoGJwbkWw5mPi7/dRxdDPHE4NCRhnGQ9YzbMSgvKq8R64rFQuMJGwEViIxIsXquja3YhA8dTS5Ll5taJ/dgxy72RwHc0hoje3pGVDwW5fji8L/m/Lyi8cflmYGFA7N15w/jiarLE+h7Z5TLenXJAzf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Location = _t, #"Date Start" = _t, #"Deficency #" = _t, Finding = _t, Procedure = _t]),
    #"Added Finding" = Table.AddColumn(Source, "Finding_New", each Text.Trim(Text.AfterDelimiter([Finding], ") "))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Finding",{"Finding"}),
    #"Split Column by Delimiter a." = Table.SplitColumn(#"Removed Columns", "Finding_New", Splitter.SplitTextByDelimiter("a. ", QuoteStyle.Csv), {"Finding", "Finding.a"}),
    #"Split Column by Delimiter b." = Table.SplitColumn(#"Split Column by Delimiter a.", "Finding.a", Splitter.SplitTextByDelimiter("b. ", QuoteStyle.Csv), {"Finding.a", "Finding.b"}),
    #"Split Column by Delimiter c." = Table.SplitColumn(#"Split Column by Delimiter b.", "Finding.b", Splitter.SplitTextByDelimiter("c. ", QuoteStyle.Csv), {"Finding.b", "Finding.c"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter c.", {"Location", "Date Start", "Deficency #", "Procedure"}, "Attribute", "Finding_new"),
    #"Added Procedure_new" = Table.AddColumn(#"Unpivoted Columns", "Procedure_new", each if Text.Contains([Finding_new], "(") and Text.Contains([Finding_new], ")") then
    Text.Middle(
        [Finding_new],
        Text.PositionOf([Finding_new], "(") + 1,
        Text.PositionOf([Finding_new], ")") - Text.PositionOf([Finding_new], "(") - 1
    )
  else
    null),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Procedure_new",{"Location", "Date Start", "Deficency #", "Finding_new", "Procedure_new"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Finding_new", "Finding"}, {"Procedure_new", "Procedure"}})
in
    #"Renamed Columns"

Note, the solution above is not very dynamic and you would need to add additional steps for the other alphabetical bullets like d., e., f. etc. This approach however has its limitations as soon as you get the to the letter i. Additionally, I made a few other assumption based on the data provided. My guts tell me that this approach won't take you all the way since your real data probably has some stuff in it which this solution does not take into consideration (yet). Still, it might get you closer 🙂

Hope this helps!

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/