Extract matching words from string separeted by Delimters

[Manish_Agarwal]

Hello All,

I have a list of groups with names starting from CHXXL-XXXXXXXX-ADM , CHHCL-Admin- , CHHCL-DC-OS-Admin and others.

I have multiple rows of these in one column .

I want to arrange these in different columns according to the starting letters .

E.G

All groups starting with CHXXL , CHHCL , CHHCL-DC and others are in different columns.

Some lists may have fewer groups and some may have groups missing.

[watkinnc]

Table.AddColumn(LastStep, "NewName", each if Text.StartsWith([ColumnName], "StringToLookFor") then [ColumnName] else null)

Repeat for all of the groups. That's it!

[Jimmy801]

Hello @Manish_Agarwal 

difficult to understand without seeing your data and what is the expected output. I tried to put some code together if you have a column with lists, that contain different data as you shown. I extracted the data from the lists and defined a column with new column name. Then I pivoted it. Check it out

let
    Source = #table(type table[Groups=list], {{{"CCH-XXX", "AAA-GH", "CCD-XXX"}},{{"CCH-111", "AAA-GI"}}}),
    #"Expanded Groups" = Table.ExpandListColumn(Source, "Groups"),
    #"Added Index" = Table.AddIndexColumn(#"Expanded Groups", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "NewColumnNames", each Text.Split([Groups],"-"){0}),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[NewColumnNames]), "NewColumnNames", "Groups")
in
    #"Pivoted Column"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[ziying35]

Hi, @Manish_Agarwal 

It's not convenient for us to just look at your description to quickly provide you with an appropriate solution, so we suggest you send a desensitization example file with source data and expected results to the cloud drive and then share the link here.

[Manish_Agarwal]

I have the members list and I want the other Columns

Members	TRACTER	CHHCL-ADMIN	CHHCL-DC-OS-ADM	CHXXL-	Remaining
TRACTER,CHHCL-Admin-XYZ,CHHCL-DC-OS-Adm,CHXXL-ASCNMCM2-ADM	TRACTER	CHHCL-ADMIN-XYZ	CHHCL-DC-OS-ADM	CHXXL-ASCNMCM2-ADM
TRACTER,CHxxL-CHHCM974-ADM,Xys	TRACTER			CHXXL-CHHCM974-ADM	Xys
TRACTER,CHHCL-Admin-IAM,CHxxL-CHHCMA08-ADM,ratina	TRACTER	CHHCL-ADMIN-IAM		CHXXL-CHHCMA08-ADM	ratina
TRACTER,CHxxL-CHHCMA10-ADM,Polo , golf	TRACTER			CHXXL-CHHCMA10-ADM	Polo ,golf
TRACTER,CHHCL-Admin-IAM,CHxxL-CHHCMA13-ADM,Else , man	TRACTER	CHHCL-ADMIN-IAM		CHXXL-CHHCMA13-ADM	Else ,man
TRACTER,CHxxL-CHHCMA17-ADM	TRACTER			CHXXL-CHHCMA17-ADM

[Jimmy801]

Hello @Manish_Agarwal 

check out this. but there is no logic why golf and tracter are handled different....

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZJdC4IwGIX/yvB6A63AuhxTUHAW6oUlXgh9IPgB2YX9+97NlYpKdSFsZ+9zzg4zSbQooCyyA8wch3mEnsu8IvHxpPYWI/tQqLCPYzgPmc8ZXxFqcQ2/YVgp3OKuL/CPogzk9KwF0lI8vEXbekSgfGduxAiOn80oCamvcxuOgiiGx359K5fyoT/Vt9L/nj3yKlssA9Q0ULEgKnqhAzV0mXGoixphdKuL67cuChEHHSWhXzsZa5lnF80F8sqsQn8W6wxA7CzAYbmbOfkLZvuY/UunLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Members = _t, TRACTER = _t, #"CHHCL-ADMIN" = _t, #"CHHCL-DC-OS-ADM" = _t, #"CHXXL-" = _t, Remaining = _t]),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Members"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Members", type text}}),
    TransformMembers= Table.TransformColumns
    (
        #"Changed Type",
        {
            {
                "Members",
                each Text.Split(_,",")
            }
        }
    ),
    #"Added Index" = Table.AddIndexColumn(TransformMembers, "Index", 0, 1),
    #"Expanded Members" = Table.ExpandListColumn(#"Added Index", "Members"),
    #"Added Custom" = Table.AddColumn(#"Expanded Members", "New ColumnNmae", each Text.Split(_[Members],","){0}),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[#"New ColumnNmae"]), "New ColumnNmae", "Members"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

Copy paste this code to the advanced editor in a new blank query to see how the solution works. 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Manish_Agarwal]

Hello ,

This is giving me a table and when expanded there are different columns for each members.

What is want is the members from the Source Column to be arranged Under the same columns as per their starting characters.

Ex

All groups starting with CHHCL (Soltuion Admin) ,CHXXL (Server-ADM) , CHHCL-DC (OS Admin) , CHHCL-ADMIN (Solution-ADMIN) and anything else should be in Remaining .

So basically I want 5 columns in total .

Local Admin  OS ADMIN  Server-ADM  Solution-Admin  Remaining 

[Jimmy801]

Hello @Manish_Agarwal 

applying your logic result only in 3 columns. Check out the solution. In case change the list in the step group

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZJdC4IwGIX/yvB6A63AuhxTUHAW6oUlXgh9IPgB2YX9+97NlYpKdSFsZ+9zzg4zSbQooCyyA8wch3mEnsu8IvHxpPYWI/tQqLCPYzgPmc8ZXxFqcQ2/YVgp3OKuL/CPogzk9KwF0lI8vEXbekSgfGduxAiOn80oCamvcxuOgiiGx359K5fyoT/Vt9L/nj3yKlssA9Q0ULEgKnqhAzV0mXGoixphdKuL67cuChEHHSWhXzsZa5lnF80F8sqsQn8W6wxA7CzAYbmbOfkLZvuY/UunLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Members = _t, TRACTER = _t, #"CHHCL-ADMIN" = _t, #"CHHCL-DC-OS-ADM" = _t, #"CHXXL-" = _t, Remaining = _t]),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Members"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Members", type text}}),
    TransformMembers= Table.TransformColumns
    (
        #"Changed Type",
        {
            {
                "Members",
                each Text.Split(_,",")
            }
        }
    ),
    #"Added Index" = Table.AddIndexColumn(TransformMembers, "Index", 0, 1),
    #"Expanded Members" = Table.ExpandListColumn(#"Added Index", "Members"),
    Groups = {"CHHCL" ,"CHxx", "CHHCL-DC" , "CHHCL-ADMIN" },
    #"Added Custom" = Table.AddColumn(#"Expanded Members", "New ColumnNmae", each let check= List.Select(List.Transform(Groups, (trans)=> if Text.StartsWith(Text.Lower(_[Members]), Text.Lower(trans)) then trans else null), each _ <> null) in if List.IsEmpty(check) then "remaining" else check{0}),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[#"New ColumnNmae"]), "New ColumnNmae", "Members", each Text.Combine(_, ",")),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Manish_Agarwal]

Hello , Thank you for looking into this and your time.

I want 5 columns .

Local Admin (Exactly Administrator)

OS ADMIN (Anything starting with CHHCL-DC-OS)

Server-ADM (CHXXL-)

Solution Admin (CHHCL)

Remaining (Anything left from the above) 

Let me know how may I put your query in my data source

[Jimmy801]

Hello @Manish_Agarwal 

sorry, I can't find any logic. What does "Local Admin" exactly mean? These both starts with the same text  - CHHCL-DC-OS - Solution Admin (CHHCL). Please define the exact logic in order that we can help you, otherwise this is just a best guess for us here that want to help you.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Manish_Agarwal]

Hello 

Local Admin is a different group - one Column ( all with the text "Administrator")

I want a separate column starting with words - "CHHCL" 

and another with "CHHCL-DC-OS"

and 2 other columns as stated earlier.

Thanks

[Jimmy801]

Hello @Manish_Agarwal 

still not clear to me.... Administrator is not contained nowhere. ADM and Admin. Please define exact the rules how the column should be split...like.... 1. if containted "admin" 2. starting with CHXXL 3. ..... 

BR

Jimmy

[Manish_Agarwal]

Hello I have administrator in my strings separated by ,

I want total 5 columns .

1. if containted "admin" 2. starting with CHXXL 3. ..... CHHCL 4. CHHCL-DC 5. Anything esle apart from these.

[Jimmy801 · Accepted solution]

Hello @Manish_Agarwal 

I've applied your logic to your data and the outcome is 4 columns because after applying step 1 and 4, nothing is left for 3 (i had to put step 4 before 3 - otherwise this would be a contradiction. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZJdC4IwGIX/yvB6A63AuhxTUHAW6oUlXgh9IPgB2YX9+97NlYpKdSFsZ+9zzg4zSbQooCyyA8wch3mEnsu8IvHxpPYWI/tQqLCPYzgPmc8ZXxFqcQ2/YVgp3OKuL/CPogzk9KwF0lI8vEXbekSgfGduxAiOn80oCamvcxuOgiiGx359K5fyoT/Vt9L/nj3yKlssA9Q0ULEgKnqhAzV0mXGoixphdKuL67cuChEHHSWhXzsZa5lnF80F8sqsQn8W6wxA7CzAYbmbOfkLZvuY/UunLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Members = _t, TRACTER = _t, #"CHHCL-ADMIN" = _t, #"CHHCL-DC-OS-ADM" = _t, #"CHXXL-" = _t, Remaining = _t]),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Members"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Members", type text}}),
    TransformMembers= Table.TransformColumns
    (
        #"Changed Type",
        {
            {
                "Members",
                each Text.Split(_,",")
            }
        }
    ),
    #"Added Index" = Table.AddIndexColumn(TransformMembers, "Index", 0, 1),
    #"Expanded Members" = Table.ExpandListColumn(#"Added Index", "Members"),
    #"Added Custom" = Table.AddColumn
    (
        #"Expanded Members", 
        "New ColumnNmae", 
        (add)=> if Text.Contains(Text.Lower(add[Members]), "admin") then "admin" else 
            if Text.StartsWith(Text.Lower(add[Members]), "chxxl") then "start with CHXXL" else 
            if Text.StartsWith(Text.Lower(add[Members]), "chhcl-dc") then "start with CHHCL-DC" else 
            if Text.StartsWith(Text.Lower(add[Members]), "chhcl") then "start with CHHCL" else "remaining"
    ),
    
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[#"New ColumnNmae"]), "New ColumnNmae", "Members", each Text.Combine(_, ",")),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy