Splitting a column in Power BI

[scoutmedic]

I'm working with a company which captures their routine inspection findings in an excel table. I'm importing the table into Power BI and need to split a column to display individual findings. The company is not willing to change how they capture their findings and I'm at a loss for splitting the column. Here is some sample data for one specific site:

Location	Date Start	Deficency #	Finding	Procedure
New York	1/1/2024	1	1) First Finding. Examples include: a. first example (SOP-001) b. second example (SOP-002) c. third example (Guidance Manual) i.xxx ii. Yyyyy iii. zzzzzzz	SOP-001 SOP-002 Guidance Manual
New York	1/1/2024	2	2) Second Finding. Examples include: a. first example (SOP-001) b. second example (SOP-002)	SOP-001 SOP-002
New York	1/1/2024	3	3) Third finding. (Form SOP-004)	Form SOP-004

In the findings column, the goal is that each item (e.g. first example, second example, etc.) will split into a separate row and the procedure # will populate in the procedure field. Stripping the leading indicators (a, b, etc.) and procedure from the Finding column would be nice as well. The result should look like this:

Location	Date Start	Deficency #	Finding	Procedure
New York	1/1/2024	1	First Finding. Examples include:
New York	1/1/2024	1	first example (SOP-001)	SOP-001
New York	1/1/2024	1	second example (SOP-002)	SOP-002
New York	1/1/2024	1	third example (Guidance Manual) i.xxx ii. Yyyyy iii. Zzzzzzz	Guidance Manual
New York	1/1/2024	2	Second Finding. Examples include:
New York	1/1/2024	2	first example (SOP-001)	SOP-001
New York	1/1/2024	2	second example (SOP-002)	SOP-002
New York	1/1/2024	3	Third finding. (Form SOP-004)	Form SOP-004

I'm open to using whatever method will accoplish the task. 

[Anonymous · Accepted solution]

Hi,

Thanks for the solution Omid_Motamedise  and @ tackytechtom  offered, and i want to offfer some more information for user to refer to.

hello @scoutmedic ,  you can put the following code to advanced editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZDLDoIwEEV/ZdIVJFqhunKvrnwkuDGUBULRRhxMgQh+vS34wqBNpp3mzkzvqe+TlbjCLlMnMiDuyB0xh01MasKGuVR5oXeMJR4ox1kVni+pyEFilJaxmHIMadIUiVayvPVm6DiuzXFPcxFlGH9JTEsRLY5SvZVFKWOBkYBliGWY6gpJq6rSh6S7uq5NIunNLI7a2uMRjo+RHL8mkGDwC42ZsMFrvT3ZoBcNOmzQgYMuHbzwevz9sTPWsW0+I2mtWPNMnaFtnJhhn3cSBHc=", 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]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Date Start", type date}, {"Deficency #", Int64.Type}, {"Finding", type text}, {"Procedure", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Finding",  Splitter.SplitTextByAnyDelimiter({")", ":"}, QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Finding"),
    #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "Custom", each let a=[Finding],
b= List.Select(Text.Split([Procedure],"#(lf)"),each Text.Contains(a,_))
in try b{0} otherwise null),
    #"Grouped Rows1" = Table.Group(#"Added Custom", {"Deficency #"}, {{"Count", each Table.FillDown(_,{"Custom"}), type table [Location=nullable text, Date Start=nullable date, #"Deficency #"=nullable number, Finding=nullable text, Procedure=nullable text, Custom=nullable text]}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows1", "Count", {"Location", "Date Start", "Finding", "Procedure", "Custom"}, {"Location", "Date Start", "Finding", "Procedure", "Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Count", each ( Text.Length(Text.Select(Text.Start([Finding],1),{"0".."9"})) <> 1)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Custom","Deficency #"}, {{"Data", each _, type table [Location=nullable text, Date Start=nullable date, #"Deficency #"=nullable number, Finding=nullable text, Procedure=nullable text, Custom=nullable text]}, {"Combine", each Text.Combine([Finding])}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Location", "Date Start", "Procedure"}, {"Location", "Date Start", "Procedure"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Data",{"Procedure"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"Combine", "Custom", "Deficency #"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Duplicates",{{"Combine", Text.Trim, type text}}),
    #"Trimmed Text2" = Table.TransformColumns(#"Trimmed Text",{{"Combine",each if List.Contains({"a.","b.","c.","d."},Text.Start(_,2)) then Text.AfterDelimiter(_,".") else _}}),
    #"Trimmed Text1" = Table.TransformColumns(#"Trimmed Text2",{{"Combine", Text.Trim, type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Trimmed Text1",{"Deficency #", "Location", "Date Start", "Combine", "Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Combine", "Finding"}, {"Custom", "Procedure"}})
in
    #"Renamed Columns"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Omid_Motamedise]

Select column Finding, go to split column and make the setting provided as below (from special character select line feed).

[tackytechtom]

Hi @scoutmedic ,

How about this?

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/

[scoutmedic]

I managed to get this to work in most cases. Need a few tweaks, though. 

Here is an updated table.

Location	Date Start	Deficency #	Finding	Procedure
New York	1/1/2024	1	1) First Finding. Examples include: a. first example (SOP-001) b. second example (SOP-002) c. third example (Guidance Manual) i.xxx ii. Yyyyy iii. zzzzzzz	SOP-001 SOP-002 Guidance Manual
New York	1/1/2024	2	2) Second Finding. Examples include: a. first example (SOP-001) b. second example (SOP-002)	SOP-001 SOP-002
New York	1/1/2024	3	Third finding. (Form SOP-004)	Form SOP-004

1. Some findings do not have the leading number and ")". See Def # 3. Applying this solution returns a blank cell. I'm thinking I need an IF/THEN expression. I can't use IF ")" is not present, because they all end with ")". 

2. Once columns are split and viewed in a table, they are not in order. I need to assign an index to keep them in order. I'm not opposed to using the letters/numbers in the Finding to do this. Or maybe another index is better.

Thanks in advance.

[Anonymous]

Hi @scoutmedic 

Thanks for the solution tackytechtom  offered ,and hello @scoutmedic , I offered a solution above, you can also refer  it. 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.