How do you identify the field name for the Column values? Is that static One (Always 7)
If it is static, You can just read one by one column number with the filter and do the left join.
if it is a dynamic, may be you can try with Generic Load.
Check this blog : The Generic Load
Directory; T1: Generic LOAD emplate, [Request Date], PAC, PROJECT, ASSET, 'Field'&[Column Number] as Rec, [Column Value] FROM temp_comm\Book1.xlsx (ooxml, embedded labels, table is Sheet1); Combined: Load distinct emplate FROM temp_comm\Book1.xlsx (ooxml, embedded labels, table is Sheet1); FOR i = NoOfTables()-1 to 0 STEP -1 LET vTable=TableName($(i)); IF WildMatch('$(vTable)', 'T1.*') THEN LEFT JOIN ([Combined]) LOAD * RESIDENT [$(vTable)]; DROP TABLE [$(vTable)]; ENDIF NEXT i //Field Name Changes NoConcatenate Summary: LOAD emplate, [Request Date], PAC, PROJECT, ASSET, Field1 as Facility, Field2 as [Item Type], Field3 as [Sub Item Type], Field4 as [Desc], Field5 as [No. of Units], Field6 as [Units], Field7 as [CostPerUnit] Resident Combined; DROP Table Combined;