Forum Discussion
Splitting a column in Power BI
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.
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/
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. zzzzzzzSOP-001
SOP-002
Guidance ManualNew York 1/1/2024 2 2) Second Finding. Examples include:
a. first example (SOP-001)
b. second example (SOP-002)SOP-001
SOP-002New 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.
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.
