Forum Discussion
Help with Excel Data in wide view, over multiple sheets
Hi D_HL
This article will be helpful: Combine Multiple or All Sheets from an Excel File into a Power BI solution Using Power Query Dynamically - RADACAD
Notice that in the following step, you need to keep "Name" and "Data" columns. The "Name" column contains sheet names that represent the attributes you want.
After expanding Data column and filtering out unnecessary rows per your need, you can select Both Name and Data columns at the same time, then unpivot other columns to get a long view table.
Hope this helps.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Thanks Jing,
The tricky element to it is that I need to make the sheet name the column header for the values presented in the sheet. My end game would be something like this...
| Primary Sector | Secondary Sector | Date | Total | Outstanding | Good Quality | Bad Quality |
| A | A | 01/01/2023 | £8.5 | £4.5 | £4.0 | £0.5 |
| A | B | 01/01/2023 | £5.5 | £2.5 | £2.5 | £0 |
| A | C | 01/01/2023 | £2.1 | £2.0 | £0.0 | £2.0 |
| B | A | 01/01/2023 | £8.4 | £5.4 | £1.0 | £7.4 |
Thanks
Dan