Forum Discussion
Solved
Formatting Excel Table for Row Level Detail in Power BI
Hello,
I am trying to make several Excel documents workable in Power BI. I am okay with pulling everything into Power Query but I am struggling to get everything working at row level. I have attached an image of the table in Excel below:
As you can see, each column is a day (one Excel document for each year) which will have a number against it based on the type of day it was, for example, if the individual had a day of annual leave on the 3rd Jan, it would be marked with a 1 (a full day).
To make this workable in Power BI, I ideally need this to read like the following:
| Name | Year | ID | Task/Booking | Status | Date | Time Sum | |||
| Example | 2023 | Annual Leave | Annual Leave | Open | 3rd Jan | 1 | |||
| Example | 2023 | Project1 | Project1D | Closed | 4th Jan | 0.5 | |||
| Example | 2023 | Sickness | Sickness | Open | 4th Jan | 0.5 |
That way I can easily calculate utilisation by different projects and types of days. Any help would be greatly appreciated.
