Using File name (ie. Sheet), Row and column position to spread Value columns in a table

lavorarchi

Hi I am working on a flow where I am taking data from individuals sheets of an Excel file and then trying to "re-construct" a table with a selective number of columns from each sheet based on a particular column header available within each sheet. So far I have got as far as this:

FileName	RowNum	ColumnNum	Value
C:\Users\...xlsx|||Sheet1	1	4	Sub 1
C:\Users\...xlsx|||Sheet1	2	4	GBP
C:\Users\...xlsx|||Sheet1	4	2	Sales
C:\Users\...xlsx|||Sheet1	4	4	10000

I now need to stack each sheet on top of another so it looks like this:

	Sheet1	Sub 1
	Sheet1	GBP
Sales	Sheet1	10000

The position and label for Sub 1 is likely to be different in other sheets. So although it is titled Sub 1 in this scenario, in another file it might be called Sub 2.

Thank you all in advance.

OllieClarke

Hey @lavorarchi 

I'm not sure I fully understood your problem, but you can use a cross-tab tool to reconstruct a table using the column number and row number to place your values

If you rename these fields with a dynamic rename tool taking field names from first row of data, then you end up with this: 

If you have multiple sheets though, then it might be better to not rename the columns:

Hope that helps,

Ollie

amojumder.yxmd