Forum Discussion
Extract matching words from string separeted by Delimters
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.
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
13 Replies
- ziying35
Impactful Individual
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.
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 
Jimmy801Community Champion
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
- 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
