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...
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

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.