Google Sheets : Need help in automating the transpose option from columns to rows

I am looking for an option in Google sheets where I can automate pivot/ transpose the values in columns to rows. something similar to power pivot in excel.

I have shared a sample and my requirement in the below google sheet link.

https://docs.google.com/spreadsheets/d/e/2PACX-1vQk5CGiXwkJcUT63ghfx77QIw2ojXMkXJJHYMCAVKfjNu_GnVroOEQD-g_SGoZNVw0pvH6_YBFzdMGs/pubhtml

Appreciate your help here.

Here's another solution:

=ARRAYFORMULA(QUERY(SPLIT(TOCOL(A2:A&"|"&B1:D1&"|"&B2:D),"|"),"where Col2<>''"))