Parse data from invoice

[BautistaC888]

Hello,

I have the following input data:

And i need to parse it to get this output:

I wonder if there is a way to get a row as header and the information bellow as row.

Thank you.

[echuong1]

You can use a sample to remove extra rows at the top, and a dynamic rename to get the first row of data (after the sample) to be the headers.

You can use a filter with regex to grab the proper rows with actual data.

Are you able to upload the excel file? I can mock a workflow for you.

[arathirajeev]

There might be a better way to do it but the way I would go about it is filter out the 'not-required' rows based on the first column value being a letter or a number and then use the Dynamic Rename tool to use the first row as the header. 

[BautistaC888]

I can't give you the Excel.
But i made this:

[Qiu · Accepted solution]

@BautistaC888 

Maybe something like this

[echuong1]

Your data has a lot of trailing spaces, which I cleaned with a data cleansing.

I skipped the first line with a sample, and moved the headers up with the dynamic rename. I used regexmatch() to filter for any line with number in the subtotal field.