Excel BI - PowerQuery Challenge 372

excel-challenges
power-query
Employee Project Hours Date Top Project Total Hours
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 372

Challenge Description

Employee Project Hours Date Top Project Total Hours

Solutions

library(tidyverse)
library(readxl)

path <- "300-399/372/PQ_Challenge_372.xlsx"
input <- read_excel(path, range = "A1:D51")
test <- read_excel(path, range = "G1:I8")

result = input %>%
  mutate(`Total Hours` = sum(Hours), .by = Employee) %>%
  mutate(HorPerProject = sum(Hours), .by = c(Employee, Project)) %>%
  filter(HorPerProject == max(HorPerProject), .by = Employee) %>%
  select(Employee, Project, `Total Hours`) %>%
  distinct() %>%
  summarise(
    `Top Project` = paste(sort(Project), collapse = ", "),
    `Total Hours` = first(`Total Hours`),
    .by = Employee
  ) %>%
  arrange(Employee) %>%
  janitor::adorn_totals("row", fill = NA)

all.equal(result, test, check.attributes = FALSE)
#> [1] TRUE

  • Logic:

    • Reads the workbook range needed for the challenge

    • Aggregates or ranks values at the relevant grouping level

    • Builds helper columns that drive the final output

  • Strengths:

    • The R solution stays close to the workbook logic and keeps the transformation compact.

  • Areas for Improvement:

    • The code assumes the workbook layout and selected ranges remain stable.

  • Gem:

    • The best part of the solution is choosing the right intermediate shape before formatting the final output.
import pandas as pd

path = "300-399/372/PQ_Challenge_372.xlsx"

input = pd.read_excel(path, usecols="A:D", nrows=50)
test = pd.read_excel(path, usecols="G:I", nrows=7).rename(columns=lambda c: c.replace('.1', ''))

total_hours = input.groupby("Employee")["Hours"].sum()
g = input.groupby(["Employee", "Project"])["Hours"].sum()
top = g[g == g.groupby("Employee").transform("max")].reset_index()
top["Top Project"] = top.sort_values("Project").groupby("Employee")["Project"].transform(lambda x: ", ".join(x))
top["Total Hours"] = top["Employee"].map(total_hours)

result = top.groupby("Employee", as_index=False).first()[["Employee", "Top Project", "Total Hours"]]
result = pd.concat([result, pd.DataFrame([{"Employee": "Total", "Top Project": None, "Total Hours": result["Total Hours"].sum()}])], ignore_index=True)
 
result = result.fillna("")
test = test.fillna("")
print(result.equals(test))
# True

  • Logic:

    • Reads the workbook range needed for the challenge

    • Aggregates or ranks values at the relevant grouping level

  • Strengths:

    • The Python version follows the same workbook rule in a direct pandas-oriented implementation.

  • Areas for Improvement:

    • As with the R version, any workbook layout change would require small adjustments.

  • Gem:

    • The implementation stays close to the source challenge instead of adding unnecessary abstraction.

Difficulty Level

This task is easy to moderate:

  • The transformation rule is readable, but the final layout still requires a careful implementation.