Google Sheet - Transpose with Query or ArrayFormula?

Question

I need to create a Query that will re-organize a table. It has been over 5 years since I was heavily using Query and Arrayformula's in Google Sheets and am not getting the results needed.

Google Sheet example Data: https://docs.google.com/spreadsheets/d/1YI1mPJqL8x0GfxMSE9K2xCNzwiUvo0RLhd5DQ0O1mC4/edit?usp=sharing

Example Google Sheet: https://docs.google.com/spreadsheets/d/1YI1mPJqL8x0GfxMSE9K2xCNzwiUvo0RLhd5DQ0O1mC4/edit?usp=sharing

Original format:

Format needed:

Answer 1

Here's one way to do that:

=ARRAYFORMULA(LET(
   brands,B11:B22,accounts,C11:C22,dates,D10:J10,data,D11:J22,
   unpivot,UNIQUE(TOCOL(brands&"|"&dates)),
   HSTACK(
     SPLIT(unpivot,"|"),
     MAP(unpivot&"|"&D27:G27,LAMBDA(lookup,TEXTJOIN(,,IF(lookup=brands&"|"&dates&"|"&accounts,data,)))))))