In the following Excel worksheet, A3:F6 is a typical table with column headers, though there are another two rows of headers above:

The task is to transpose columns to rows, including both the typical table with header and the extra two rows of headers:

Use SPL XLL to enter the following formula:

=spl("=c=transpose(?2),?1.conj( (a=~).m(3:).(a.to(2)|c(#)|~))",A4:F6,C1:F3)

The transpose()function performs transposition on headers within C1:F3; conj() function concatenates members of the set, where ~ represents each row in table A4:F6; m(3:) means getting members of each row from the 3^rd to the end.

Source:https://stackoverflow.com/questions/78433579/unpivot-columns-with-multiple-relevant-rows-with-headers-powerquery-m-language