Take a series of rows and break each row into multiple rows

djclements again any help is so appreciated.  In the attached, I removed the outer EXPAND and tried to add in the column with the lang field that I defined. I get a VALUE error. Tried a few different ways that I could think of, but no luck.

Although the lang variable seems to work outside of the LET, I must be trying to reference it incorrectly.

I have been playing with the formula and in the last line ,,7,Current!AV2 provides a response which happens to be en_ca.  I have tried various ways to get an array for the language, but each time I do that I get a #VALUE, which tells me I am out of bounds and it seems to want just a constant or a single cells content.

=LET(
          tbl,Current!$A1:$AV$1600,
          data, DROP( tbl, 1, 2),
           keys, DROP(TAKE(tbl,, 2), 1),
           attr, DROP(TAKE(tbl, 1),, 2),
          code, XLOOKUP(attr, Table_Specifications[Attribute name],       Table_Specifications[[Classification Attribute ]], "not found"),
           type, XLOOKUP(attr, Table_Specifications[Attribute name], Table_Specifications[Feature Type], "not found"),
           lang,TAKE(DROP(tbl,1),,1),
a, SEQUENCE(ROWS(data)),
b, SEQUENCE(, COLUMNS(data)),
EXPAND(
EXPAND(
HSTACK(
CHOOSEROWS(keys, TOCOL(IF(b,a))),
TOCOL(IF(a,code)),
TOCOL(data)&","&TOCOL(IF(a,type))
),,6,""
),,7,Current!AV2
)
)

NotSoFastEddie DROP the Language column from the data & attr variables and add it to the keys variable. Then use CHOOSECOLS on the final results to rearrange the columns as desired. See attached...

djclements Not sure if my earlier reply ever got saved and sent as I don't see it in my history.

The data entry sheet PI_Package is the basis for linkProductFeatures_Base which contains PCODE and BUNDLE IDENTIFIER rows and attaches the language based off the array lang.  PI_PACKAGE has both english and french columns for assembly specifications.  it turns out, that I needed to add dimension type (ft,in,lb, cm/mm/..) for both english and french.  Background ->In PI_Package, I used a custom format #0.00 " in" (/ft/lb).  This works great on the data entry portion.  When the cells are copied to linkProductFeatures_Base, the formatting is lost which is why I added lines in the LET statement to convert to text.  This stops the issue and carries the formatting on to linkproductfeatures (ends up being exported to a CSV).  

Back to the ask, I thought I would use the same approach that you provided for code with the idea that if the language on the row is fr_CA, then use the french columns, otherwise use the english columns.  You can see in the output of linkProductFeatrues_Base that it is iterating through the language, but the result is not desirable.  Sheet 3 provided me some insight.  I am not sure how to correct this.  I know the magic is in the TOCOL(IF(a,b))),TOCOL(IF(b,lang).

=LET(
table, PI_Package!$A$1:$HZ$1600,
head,TAKE(table,1),
data,FILTER(DROP(table,6),
(PI_Package!$C$7:$C$1600="PCODE")*(PI_Package!$C$7:$C$1600="PTY")+(PI_Package!$C$7:$C$1600="BOTH")+((PI_Package!$A$7:$A$1600="BUNDLE IDENTIFIER")*(PI_Package!$C$7:$C$1600="PTY")+(PI_Package!$C$7:$C$1600="BOTH")),"NO RECORDS"),
lang,{"en_CA";"en";"fr_CA"},
specs_heading0,TAKE(DROP(head,,176),,1),specs_heading1,TAKE(DROP(head,,26),,14),specs_heading2,TAKE(DROP(head,,44),,1),
specs_heading3,TAKE(DROP(head,,49),,5),specs_heading4,TAKE(DROP(head,,183),,8),specs_headingfr,TAKE(DROP(head,,191),,15),
specs_data0,TAKE(DROP(data,,176),,1),specs_data1,TAKE(DROP(data,,26),,14),specs_data2,TAKE(DROP(data,,44),,1),
specs_metricdata,TAKE(DROP(data,,49),,5),specs_impdata,TAKE(DROP(data,,183),,8),
specs_datafr,TAKE(DROP(data,,191),,15),specs_impdatafr,TAKE(DROP(data,,211),,8),specs_metricdatafr,TAKE(DROP(data,,206),,5),
a,SEQUENCE(ROWS(lang)), b,SEQUENCE(,ROWS(data)),
code,IF(CHOOSECOLS(data,1)="PCODE",CHOOSECOLS(data,4),CHOOSECOLS(data,5)),
assht_cm,IF(CHOOSECOLS(specs_metricdata,1)<>"",TEXT(CHOOSECOLS(specs_metricdata,1),"#0.00")&" cm"),
assln_cm,IF(CHOOSECOLS(specs_metricdata,2)<>"",TEXT(CHOOSECOLS(specs_metricdata,2),"#0.00")&" cm"),
asswd_cm,IF(CHOOSECOLS(specs_metricdata,3)<>"",TEXT(CHOOSECOLS(specs_metricdata,3),"#0.00")&" cm"),
asswt_kg,IF(CHOOSECOLS(specs_metricdata,4)<>"",TEXT(CHOOSECOLS(specs_metricdata,4),"#0.00")&" kg"),
assdi_cm,IF(CHOOSECOLS(specs_metricdata,5)<>"",TEXT(CHOOSECOLS(specs_metricdata,5),"#0.00")&" cm"),
assht_ft,IF(lang="fr_CA",CHOOSECOLS(specs_impdatafr,1),CHOOSECOLS(specs_impdata,1)),
assht_in,IF(lang="fr_CA",CHOOSECOLS(specs_impdatafr,2),CHOOSECOLS(specs_impdata,2)),
assln_ft,IF(lang="fr_CA",CHOOSECOLS(specs_impdatafr,3),CHOOSECOLS(specs_impdata,3)),
assln_in,IF(lang="fr_CA",CHOOSECOLS(specs_impdatafr,4),CHOOSECOLS(specs_impdata,4)),
asswd_in,IF(lang="fr_CA",CHOOSECOLS(specs_impdatafr,5),CHOOSECOLS(specs_impdata,5)),
asswd_mm,IF(lang="fr_CA",CHOOSECOLS(specs_impdatafr,6),CHOOSECOLS(specs_impdata,6)),
asswt_lb,IF(lang="fr_CA",CHOOSECOLS(specs_impdatafr,7),CHOOSECOLS(specs_impdata,7)),
assdi_in,IF(lang="fr_CA",CHOOSECOLS(specs_impdatafr,8),CHOOSECOLS(specs_impdata,8)),
VSTACK(
HSTACK("#code",specs_heading0,specs_heading1,specs_heading2,specs_heading3,specs_heading4,specs_headingfr,"Language"),
HSTACK(CHOOSEROWS(HSTACK(code,specs_data0,specs_data1,specs_data2,assht_cm,assln_cm,asswd_cm,asswt_kg,assdi_cm,assht_ft,assht_in,assln_ft, assln_in,asswd_in,asswd_mm,asswt_lb,assdi_in,specs_datafr),
TOCOL(IF(a,b))),TOCOL(IF(b,lang))
)
)
)

NotSoFastEddieEvery scenario presented has been slightly different than the last. In this case, use the REDUCE function to iterate through the static lang array and return a different set of results for "fr_CA"...