Excel BI - PowerQuery Challenge 165

excel-challenges
power-query
Insert the total row containing count of employees and sum of salary at the bottom of each dept groups.
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 165

Challenge Description

Insert the total row containing count of employees and sum of salary at the bottom of each dept groups.

Solutions

library(tidyverse)
library(readxl)
library(janitor)

input = read_excel("Power Query/PQ_Challenge_165.xlsx", range = "A1:C11")
test  = read_excel("Power Query/PQ_Challenge_165.xlsx", range = "F1:I15")

r1 = input %>%
  mutate(`Max Bonus` = Salary * 0.1,
         group = cumsum(!is.na(Dept)))

make_summary = function(df, gr) {
  data <- df %>%
    filter(group == gr) 
  
  summary <- data %>%
    mutate(Dept = "Total") %>%
    summarise(Dept = first(Dept),
              Emp = as.character(n()),
              Salary = sum(Salary),
              `Max Bonus` = sum(`Max Bonus`))
  result = bind_rows(data, summary)
  return(result)
}

groups = unique(r1$group)
r2 = map_dfr(groups, ~make_summary(r1, .x))

grand_total = r2 %>%
  filter(!is.na(group)) %>%
  summarise(Dept = "Grand Total",
            Emp = as.character(n()),
            Salary = sum(Salary),
            `Max Bonus` = sum(`Max Bonus`))

result = bind_rows(r2, grand_total) %>%
  select(-group)

identical(result, test)
# [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

input_data = pd.read_excel("PQ_Challenge_165.xlsx", usecols="A:C", nrows=11)
test = pd.read_excel("PQ_Challenge_165.xlsx", usecols="F:I", nrows=15)

result = input_data.copy()
result["Max Bonus"] = result["Salary"] * 0.1
result["group"] = result["Dept"].notna().cumsum()

parts = []
for _, g in result.groupby("group"):
    summary = pd.DataFrame([{
        "Dept": "Total",
        "Emp": str(len(g)),
        "Salary": g["Salary"].sum(),
        "Max Bonus": g["Max Bonus"].sum(),
        "group": g["group"].iloc[0],
    }])
    parts.append(pd.concat([g, summary], ignore_index=True))

r2 = pd.concat(parts, ignore_index=True)
grand_total = pd.DataFrame([{
    "Dept": "Grand Total",
    "Emp": str(len(r2.dropna(subset=["group"]))),
    "Salary": r2.dropna(subset=["group"])["Salary"].sum(),
    "Max Bonus": r2.dropna(subset=["group"])["Max Bonus"].sum(),
}])
result2 = pd.concat([r2.drop(columns="group"), grand_total], ignore_index=True)

print(result2.equals(test))

  • Logic:

    • Reads the workbook range needed for the challenge

    • Aggregates or ranks values at the relevant grouping level

    • Applies the rule iteratively until the output is complete

  • 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.