Forum Discussion

FoolzRailer1's avatar
FoolzRailer1
Frequent Visitor
11 months ago
Solved

Employee Workload - Underlying data structure suggestions?

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!

 

 

 

 

 

 

 

Need Help
  • sivarajan21's avatar
    sivarajan21
    11 months ago

    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,

4 Replies

  • sivarajan21's avatar
    sivarajan21
    Post Prodigy
    11 months ago

    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,

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 months ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 months ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 months ago

    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