Employee Workload - Underlying data structure suggestions?

[FoolzRailer1]

Hello,

My employer currently uses Excel to track employee workloads. Each team member fills out a spreadsheet every month, indicating how many days they’re planning on working on specific projects (for how long they can foresee). Now, they’d like to visualize this data in Power BI to get a better overview of individual workloads, team workloads, and overall resource allocation.

However, with the current Excel setup, I’m struggling to see how this could be easily integrated into Power BI (though I’d be happy to be proven wrong). I’m looking for suggestions or sample templates for a more structured data input method—something that’s simple for employees to fill out but also easy to connect to Power BI for visualization.

Any ideas or examples would be greatly appreciated!

[v-pgoloju]

Hi @FoolzRailer1,

Just following up to see if the Response provided by community member were helpful in addressing the issue.

If one of the response helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[v-pgoloju]

Hi @FoolzRailer1,

Just following up to see if the Response provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

Best regards,

Prasanna Kumar

[v-pgoloju]

Hi @FoolzRailer1,

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to @sivarajan21   for prompt and helpful response.

Just following up to see if the Response provided by community member were helpful in addressing the issue.

If one of the response helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[sivarajan21 · Accepted solution]

Hi @FoolzRailer1 

Option1:

Create a new Excel template with separate sheets:

Create Relationships:

Projects (1) ←→ (*) Workload_Data ←→ (*) Employees (1)
Employees (1) ←→ (*) Leave_Data
Date_Table (1) ←→ (*) Workload_Data

If you don't want the above then try below:

Step 1: Load Data into Power BI

1. Get Data → Excel → Select your workbook
2. In Power Query Editor, select your data table

Step 2: Unpivot Month Columns

1. Select all month columns (Jan-22, Feb-22, etc.)
2. Transform → Unpivot Columns
3. Rename columns:
   • "Attribute" → "Month_Year"
   • "Value" → "Planned_Days"

Step 3: Clean and Transform

// Add Year and Month columns
= Table.AddColumn(#"Unpivoted Columns", "Year", each Date.Year(Date.FromText("01-" & [Month_Year])))
= Table.AddColumn(#"Added Year", "Month", each Date.Month(Date.FromText("01-" & [Month_Year])))
= Table.AddColumn(#"Added Month", "Month_Name", each Date.MonthName(Date.FromText("01-" & [Month_Year])))

// Filter out null/empty values
= Table.SelectRows(#"Added Month_Name", each [Planned_Days] <> null and [Planned_Days] <> 0)

// Create Date column for better time intelligence
= Table.AddColumn(#"Filtered Rows", "Date", each Date.FromText("01-" & [Month_Year]))

Please let me know if this is what you expect?

Best Regards,