How to create a new column with a different concatenation

[Betty888]

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 ! 

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

[Sahir_Maharaj · Accepted solution]

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

[Betty888]

Works perfectly for me , many many thanks 🙏🙏🙏