New Column Based on Semicolon Column Values

[BrendenS]

Hello,

I have 3 columns Extension ID, Extension Pattern, and Ext Pattern Class Ref.  For a single Extension ID there are various amounts of Extension Patterns.  Each Extension Pattern has a color class assigned (Ext Pattern Class Ref). 

Because of the way the data is provided in the Ext Pattern Class Ref column (separated by semicolon) it is difficult to create a new column (Extension Pattern Classification) with the correct values.  Please share a workaround for this challenge if you have experienced. 

Thank you!

[v-stephen-msft · Accepted solution]

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.           

[dufoq3 · Accepted solution]

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

[v-stephen-msft]

Hi @BrendenS ,

You can use split and unpivot to achieve this.

Here's the codes:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wco0IMTAwMFLSUQo28TAwMAUy3ItSU/OsFaBUZGpOTn65UqwOslrfYHcIgwi1jk6OAQbGuBXHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Extension ID" = _t, #"Extension Patter" = _t, #"Ext Pattern Class Ref" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Extension ID", type text}, {"Extension Patter", 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"}),
    #"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}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1, Int64.Type),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Extension ID", "Extension Patter", "Index"}, "Attribute", "Value"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Unpivoted 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 Columns" = Table.RemoveColumns(#"Changed Type2",{"Attribute.1"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each if [Index]=[Attribute.2] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Attribute.2", "Custom"})
in
    #"Removed Columns1"

Or you can download my attachment for details. You can check what each step does from the steps that have been applied.

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.           

[BrendenS]

Hi Stephen, thank you for your help. While you have solved the exact use case for this limited example, is there a way to scale this in a dynamic way to fit a series of data with differentiating Extension Pattern to Ext Pattern Class Ref situations?  See example below...

[v-stephen-msft · Accepted solution]

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.           

[amustafa]

Please explain the 'rule' why it should pick "Yellow" over "Green".

[BrendenS]

[BrendenS]

Sure, the exact order of the Extension Pattern column coordinates with the Ext Pattern Class Ref semicolon separated values.  See the screenshot below for a visual example. Note in this example the EXT00002 has three different extension patterns, two green, and one yellow. Other Extension IDs may have more than three extension patterns with differentiating colors in the Ext Pattern Class Ref column. Will include another example.  Thank you for your help!