Forum Discussion
Betty888
Helper II
Helper IIHow to create a new column with a different concatenation
Dear all,
I'm not sure what I will descrive will be easy to understand, not even whether it is possible or not, but I'll try ....
I have a table with 3 columns where information were concatenated from source with ";" delimiter.
what I need to do is to create a new column concatenating inforamtion as here below :
Current table :
| ID | col1 | col2 | col3 |
| ID1 | Prodouct1 ;Prodouct2;Prodouct2 | Dose1;Dose2;Dose3 | Label1;Label2;Label3 |
| ID2 | Prodouct1 ;Prodouct2;Prodouct3;Product4 | Dose1;Dose2;Dose3, Dose4 | Label1;Label2;Label3, Label4 |
My target :
| ID | col1 | col2 | col3 | Col4 |
| ID1 | Prodouct1 ;Prodouct2;Prodouct2 | Dose1;Dose2;Dose3 | Label1;Label2;Label3 | Product1 Dose1 Label1 Product2 Dose2 Label2 Product3 Dose3 Label3 |
| ID2 | Prodouct1 ;Prodouct2;Prodouct3;Product4 | Dose1;Dose2;Dose3, Dose4 | Label1;Label2;Label3, Label4 | Product1 Dose1 Label1 Product2 Dose2 Label2 Product3 Dose3 Label3 Product4 Dose4 Label4 |
Do you have any idea how can I createt his new Col4 🙏
Thanks a lot fpr your help !
Hello Betty888,
Can you please try this:
Col4 = VAR Products = SUBSTITUTE([col1], ";", "|") VAR Doses = SUBSTITUTE([col2], ";", "|") VAR Labels = SUBSTITUTE([col3], ";", "|") VAR ProductList = GENERATE(SPLIT(Products, "|"), SPLIT(Doses, "|"), SPLIT(Labels, "|")) RETURN CONCATENATEX(ProductList, [Column1] & " " & [Column2] & " " & [Column3], CHAR(10))
3 Replies
- Sahir_Maharaj
Super User
Hello Betty888,
Can you please try this:
Col4 = VAR Products = SUBSTITUTE([col1], ";", "|") VAR Doses = SUBSTITUTE([col2], ";", "|") VAR Labels = SUBSTITUTE([col3], ";", "|") VAR ProductList = GENERATE(SPLIT(Products, "|"), SPLIT(Doses, "|"), SPLIT(Labels, "|")) RETURN CONCATENATEX(ProductList, [Column1] & " " & [Column2] & " " & [Column3], CHAR(10))
Betty888Works perfectly for me , many many thanks 🙏🙏🙏
- Ashish_Mathur
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Combine(List.Combine(List.Zip({Text.Split([col1],";"),Text.Split([col2],";"),Text.Split([col3],";")})),",")) in #"Added Custom"I just do not know how to get the Alt+Enter after every third item in the list.
