Associating first value of a comma separated string to first value of another comma separated string

[rajulshah]

Hello,

I have table which has data as below:

And I want to associate first value of comma separated string in Column1 to first value of comma separated string in Column2 for each Id. Number of values in comma separated strings in both the columns will have same number of values, but it may be 2-digit, 3-digit or any digit number.

Hence, the following is the result I want:

Any help would be appreciated.

Thanks in advance.

[Zubair_Muhammad]

@rajulshah

Hi,

Add this Custom Column using Query Editor. Then Expqand it to new rows

=Table.Transpose(
   Table.FromColumns(

List.Zip(
{
Text.Split([Column1],","),
Text.Split([Column2],",")
}
          )
                    )
                )

[Zubair_Muhammad]

@rajulshah

Please also see attached file's Query Editor with few rows from your sample data

[Zubair_Muhammad · Accepted solution]

@rajulshah

Sorry. i gave you an unnecessary lengthy code. Following custom column will also be sufficient

=Table.FromColumns(
{
Text.Split([Column1],","),
Text.Split([Column2],",")
}
          )