I see there is a bit of confusion here, it is possible to use Power Query inside of Excel - in fact, that is where Power Query started.
Attached is the Excel Power Query solution - and yes, the steps taken here would also work in PowerBi Desktop.
Basic steps required for this solution are:
- Transpose the table (so we can get the values on the first header line added to every column)
- Fill Down (see step above)
- Merge the Header columns together
- Transpose the table back to the original version
- Promote Headers
- Unpivot the value columns
- Fix Header names
- Split the merged Value Type and Brand Headers
- Pivot the Brand column back using the values as cell contents
One warning, you had Brand 5 listed twice in the source data, and that will NOT work, you’ll need to call that something else (I used Brand 6)
Dashboard (1) - with solution.xlsx (243.0 KB)