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.
Appreciate your help here.
Here's another solution:
=ARRAYFORMULA(QUERY(SPLIT(TOCOL(A2:A&"|"&B1:D1&"|"&B2:D),"|"),"where Col2<>''"))