New Column Based on Semicolon Column Values

Hi BrendenS ,

Here's the modified whole codes:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wco0IMQACEyUdJd9gdwMDQyDDvSg1NU8pVgcma2wBFPT3D3GCqANLWytEpubk5JdbK6Bw0XU5OjkGGBgakqgLYpcxOXYZG5CgyxLFhRjylkZA0aAAR6RQwWIKQpURblWGBiC7XHyQfKUUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Extension ID" = _t, #"Extension Pattern" = _t, #"Ext Pattern Class Ref" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Extension ID", type text}, {"Extension Pattern", type text}, {"Ext Pattern Class Ref", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Ext Pattern Class Ref", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Ext Pattern Class Ref.1", "Ext Pattern Class Ref.2", "Ext Pattern Class Ref.3", "Ext Pattern Class Ref.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Ext Pattern Class Ref.1", type text}, {"Ext Pattern Class Ref.2", type text}, {"Ext Pattern Class Ref.3", type text}, {"Ext Pattern Class Ref.4", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Extension ID"}, {{"Count", each _, type table [Extension ID=nullable text, Extension Pattern=nullable text, Ext Pattern Class Ref.1=nullable text, Ext Pattern Class Ref.2=nullable text, Ext Pattern Class Ref.3=nullable text, Ext Pattern Class Ref.4=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Extension Pattern", "Ext Pattern Class Ref.1", "Ext Pattern Class Ref.2", "Ext Pattern Class Ref.3", "Ext Pattern Class Ref.4", "Index"}, {"Extension Pattern", "Ext Pattern Class Ref.1", "Ext Pattern Class Ref.2", "Ext Pattern Class Ref.3", "Ext Pattern Class Ref.4", "Index"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded Custom", {"Extension ID", "Extension Pattern", "Index"}, "Attribute", "Value"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"Attribute.1"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns1", "Custom", each if [Attribute.2]=[Index] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom] = 1)),
    #"Trimmed Text" = Table.TransformColumns(#"Filtered Rows",{{"Value", Text.Trim, type text}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Trimmed Text",{"Index", "Attribute.2", "Custom"})
in
    #"Removed Columns2"

Before:

After:

You can download my latest attachment for more information.

Best Regards,

Stephen Tao

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

Hi BrendenS, try this:

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wco0IMQACEyUdJd9gdwMDQyDDvSg1NU8pVgcma2wBFPT3D3GCqANLWytEpubk5JdbK6Bw0XU5OjkGGBgakqgLYpcxOXYZG5CgyxLFhRjylkZA0aAAR6RQwWIKQpURblWGBiC7XHyQfKUUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Extension ID" = _t, #"Extension Pattern" = _t, #"Ext Pattern Class Ref" = _t]),
    Ad_PatternSplit = Table.AddColumn(Source, "Pattern Split", each Text.Split([Ext Pattern Class Ref], "; "), type list),
    GroupedRows = Table.Group(Ad_PatternSplit, {"Extension ID"}, {{"All", each Table.AddColumn(Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type), "Extension Pattern Classification", (x)=> x[Pattern Split]{x[Index]}?, type text), type table}}),
    CombinedTables = Table.Combine(GroupedRows[All]),
    RemovedColumns = Table.RemoveColumns(CombinedTables,{"Pattern Split", "Index"})
in
    RemovedColumns