Forum Discussion

aridavid's avatar
aridavid
Frequent Visitor
5 years ago
Solved

Split column in multiple columns based on text

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)

 

IDLabels
001Profile - Type1, Site - Site 2, Priority - High, Profile - Type 2
002Profile - Type1, Site - Site 1, Site- Site 2, Profile - Type 3
003Priority - 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:

 

IDLabelsProfileSitePriority
001Profile - Type1, Site - Site2, Priority - High, Profile - Type2Profile - Type1,  Profile Type2Site - Site2Priority - High
002Profile - Type1, Site - Site1, Site- Site2, Profile - Type3Profile - Type1, Profile - Type3Site - Site1, Site- Site2 
003Priority - Low, Site - Site3, Site - Site4, Profile - Type2, Profile - Type3Profile - Type2, Profile - Type3Site - Site3, Site - Site4Priority - Low

 

Thanks!

David.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

2 Replies

  • mussaenda's avatar
    mussaenda
    Community Champion
    5 years ago

    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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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