Split Multiple Column Data

Help Activities
, ,
1

As you can see in the excel file, I want to split the highlighted data of both the columns in this excel file.
Note - (1) Remember S.N. It should not be repeated, it will keep getting updated.
(2) I want that even if these column names can be more than 5 or less than 5 columns but same process should be done in all of them.
data.xlsx (8.5 KB)

Thank you

2

Hi @UJJVAL_BHAGAT

Can you share the expected output also.

Regards

3

@UJJVAL_BHAGAT

Please Place the Expected Output

4

image

5

Hi @UJJVAL_BHAGAT

Used process is LINQ expression.

image
image760×612 36 KB

First Assign OutputDt = InputDt.Clone() copys the headers from Input and store it a datable say OutputDt.

Second Assign

OutputDt = (From row In InputDt.AsEnumerable()
            From id In row("Id").ToString().Split("/"c)
            Let products = row("product ").ToString().Split("/"c)
            Let idx = Array.IndexOf(row("Id").ToString().Split("/"c), id)
            Select OutputDt.LoadDataRow(New Object() {
                OutputDt.Rows.Count + 1,
                row("person"),
                id.Trim(),
                row("things"),
                If(idx < products.Length, products(idx).Trim(), String.Empty)
            }, False)).CopyToDataTable()

Explanation:
This LINQ query effectively splits rows that have multiple Id and product values into separate rows. Each combination of Id and product from the same original row in InputDt becomes a new row in OutputDt, with the other columns (person, things) duplicated as needed. This transformation results in a normalized table where each Id and its corresponding product are in separate rows.

Please find the below zip file.
BlankProcess3.zip (163.7 KB)

Let me know if you have any issues in understanding.

Regards

6

Hi @UJJVAL_BHAGAT

Decomposed Approach:

image
image760×1515 73.2 KB

XAML:
Sequence.xaml (12.7 KB)

Regards