Dynamic restructure (wide -> long) in Excel

Use PowerQuery to get the result:

enter image description here

You can refresh directly if you change the data source Table.


Hopefully you found an answer but for anyone else googling this there are some formulas halfway down this page: https://trumpexcel.com/source-data-for-pivot-table/ that can be used instead of Power Query.