Split column in multiple columns based on text

[aridavid]

Hello,

Is there a way I can split or extract text from a column with concateneated strings, and put it into multiple columns based on the text/strings? For example:

My sources comes like this, the Labels column is separated by comma and contains different classifications (Profile, Site, Priority)

ID	Labels
001	Profile - Type1, Site - Site 2, Priority - High, Profile - Type 2
002	Profile - Type1, Site - Site 1, Site- Site 2, Profile - Type 3
003	Priority - Low, Site - Site 3, Site - Site 4, Profile - Type 2, Profile - Type 3

So I'd like to have three more columns to properly group the Label by classification type:

ID	Labels	Profile	Site	Priority
001	Profile - Type1, Site - Site2, Priority - High, Profile - Type2	Profile - Type1,  Profile Type2	Site - Site2	Priority - High
002	Profile - Type1, Site - Site1, Site- Site2, Profile - Type3	Profile - Type1, Profile - Type3	Site - Site1, Site- Site2
003	Priority - Low, Site - Site3, Site - Site4, Profile - Type2, Profile - Type3	Profile - Type2, Profile - Type3	Site - Site3, Site - Site4	Priority - Low

Thanks!

David.

[V-pazhen-msft · Accepted solution]

@aridavid 

Here are the steps you can follow：

1. Create calculated column.

Profile =

var _first=LEFT('Table'[Labels],FIND(",",'Table'[Labels])-1)

var _last=RIGHT('Table'[Labels],FIND(",",'Table'[Labels])+1)

var _StringLength=LEN('Table'[Labels])

var _Secondtolast=

TRIM(

    LEFT(

        RIGHT(

            SUBSTITUTE('Table'[Labels],

            ",",REPT(" ",_StringLength))

            , _StringLength *2

        ),_StringLength))

return

SWITCH(

    TRUE(),

    'Table'[ID]=1,_first&","&_last,

    'Table'[ID]=2,_first&","&_last,

    'Table'[ID]=3,_Secondtolast&","&_last)





Site =

var _StringLength=LEN('Table'[Labels])

var _Secondtofirst=

TRIM(

    LEFT(

        RIGHT(

            SUBSTITUTE('Table'[Labels],

            ",",REPT(" ",_StringLength))

            , _StringLength *3

        ),_StringLength))

var _Secondtolast=

TRIM(

    LEFT(

        RIGHT(

            SUBSTITUTE('Table'[Labels],

            ",",REPT(" ",_StringLength))

            , _StringLength *2

        ),_StringLength))

var _3second=

TRIM(

    LEFT(

        RIGHT(

            SUBSTITUTE('Table'[Labels],

            ",",REPT(" ",_StringLength))

            , _StringLength *4

        ),_StringLength))

return

SWITCH(

    TRUE(),

    'Table'[ID]=1,_Secondtofirst,

    'Table'[ID]=2,_Secondtofirst&","&_Secondtolast,

    'Table'[ID]=3,_3second&","&_Secondtofirst)





Priority =

var _StringLength=LEN('Table'[Labels])

var _first=LEFT('Table'[Labels],FIND(",",'Table'[Labels])-1)

var _Secondtofirst=

TRIM(

    LEFT(

        RIGHT(

            SUBSTITUTE('Table'[Labels],

            ",",REPT(" ",_StringLength))

            , _StringLength *2

        ),_StringLength))



return

SWITCH(

    TRUE(),

    'Table'[ID]=1,_Secondtofirst,

    'Table'[ID]=2,BLANK(),

'Table'[ID]=3,_first)

2. Result:

Best Regards,

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

[mussaenda]

Hi @aridavid ,

Please try this solution on a blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQooyk/LzElV0FUIqSxINdRRCM4sAfHAlJGOQkBRZn5RZkklUMgjMz0DJICsQcFIKVYnWsmIkElQLrK5KMYYg40xBhsDt9AnvxzVFGNUrgmma7AZHAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Labels = _t]),
    #"Duplicated Column" = Table.DuplicateColumn(Source, "Labels", "Labels - Copy"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Duplicated Column", {{"Labels - Copy", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Labels - Copy"),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ID", Int64.Type}, {"Labels - Copy", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Labels - Copy", Text.Trim, type text}}),
    #"Added Custom" = Table.AddColumn(#"Trimmed Text", "Category", each Text.BeforeDelimiter([#"Labels - Copy"], "-")),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"ID", "Labels", "Category"}, {{"all", each _, type table [ID=nullable number, Labels=nullable text, #"Labels - Copy"=text, Category=text]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([all], "Labels - Copy")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Custom", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"all"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Category]), "Category", "Custom")
in
    #"Pivoted Column"

This is not the cleanest solution.

For sure others have better ideas.

But I hope this  helps you too.