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.