Formatting Excel Table for Row Level Detail in Power BI

[Griggs]

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.

[VijayP · Accepted solution]

@Griggs 

use unpivot( which ever columns to appearns in Rows instead of columns) in Powerquery