Formatting excel data

I have an Excel sheet with with below format:

In this Excel, column 1,2 and 3 remains common, and the data which i am suppose to send is in below output csv format:

Output:
QWE;1234;567890;1111;20212021 (Row 1: Column 1,2 and 3 is common with fields columns starting with ‘A’)
QWE;1234;567890;2020 (Row 1: Column 1,2 and 3 is common with fields columns starting with ‘B’)
QWE;1234;567890; ; ; (Row 1: Column 1,2 and 3 is common with fields columns starting with ‘C’)
QWE;1234;567890;1 (Row 1: Column 1,2 and 3 is common with fields columns starting with ‘D’)
QWE;1234;567890;2 (Row 1: Column 1,2 and 3 is common with fields columns starting with ‘E’)
OPI;5678;98765;2222;100100 (Row 2: Column 1,2 and 3 is common with fields columns starting with ‘A’)
OPI;5678;98765;456 (Row 2: Column 1,2 and 3 is common with fields columns starting with ‘B’)
OPI;5678;98765; ;65 (Row 2: Column 1,2 and 3 is common with fields columns starting with ‘C’)
OPI;5678;98765;3 (Row 2: Column 1,2 and 3 is common with fields columns starting with ‘D’)
OPI;5678;98765; ; (Row 2: Column 1,2 and 3 is common with fields columns starting with ‘E’)

Output SS:
op

Can anyone help me here to resolve the complexity?

Hi @aditya.gupta41 ,

I created a solution for this. You can download and import the pipeline. This pipeline will work for more fields, it is not limited to only those shown on the image here.
Sample result:
image

Formatting_Excel_Data_SL_Community_2021_08_19.slp (9.0 KB)

Edit:
I forgot to change the csv delimiter, to do this, open the CSV Formatter snap and change the Delimiter setting to ;
image

You can also remove the quote characters if you want to. To do this just leave the Quote character setting empty.
image
And it will create a result like this
image

Hello @j.angelevski,

Thank You for the help here. The code successfully worked on actual data. However, is it possible that these empty streams can be removed?

Thanks in advance

@aditya.gupta41
Yes you can remove the empty streams. First you need to uncheck the Insert null columns setting in the excel parser. This will automatically exclude the empty fields.
image
Next, I updated the expression in the mapper.

$.keys()[0].split("|").map(val => val.contains("_") ? val.substring(0, val.indexOf("_")) : val).map(keyStart =>  $.filter((val, key) => key.contains(keyStart)))