Excel BI - PowerQuery Challenge 347

excel-challenges
power-query
Activity Name Unit Quantity Unit Weight Chainage Resource Name
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 347

Challenge Description

Activity Name Unit Quantity Unit Weight Chainage Resource Name

Solutions

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

path <- "Power Query/300-399/347/PQ_Challenge_347 - Table Decomposition.xlsx"
input1 <- read_excel(path, range = "A1:F6")
input2 <- read_excel(path, range = "A8:B11")
test <- read_excel(path, range = "I1:P20")

cartesian_product <- crossing(
  input1 %>% mutate(ID = glue("ID{row_number()}") %>% as.character()),
  input2
)

result = cartesian_product %>%
  mutate(
    `Activity ID` = glue("{`Sub Table`}_{ID}") %>% as.character(),
    Quantity = Quantity * `%`,
    `Total Weight` = Quantity * `Unit Weight`
  ) %>%
  select(
    `Sub Table`,
    `Activity ID`,
    `Activity Name`,
    Unit,
    Quantity,
    `Unit Weight`,
    `Total Weight`,
    Chainage,
    `Resource Name`
  ) %>%
  arrange(`Activity ID`)

subs = result %>%
  summarise(
    `Total Weight` = sum(`Total Weight`),
    .by = `Sub Table`
  ) %>%
  mutate(
    `Sub Table`,
    `Activity ID` = "TOTAL",
    `Activity Name` = NA,
    Unit = NA,
    Quantity = NA,
    `Unit Weight` = NA,
    `Total Weight`,
    Chainage = NA,
    `Resource Name` = NA
  )

total = result %>%
  summarise(
    `Total Weight` = sum(`Total Weight`)
  ) %>%
  mutate(
    Chainage = NA,
    `Resource Name` = NA,
    Unit = NA,
    Quantity = NA,
    `Unit Weight` = NA,
    `Activity ID` = "GRAND TOTAL",
    `Activity Name` = NA,
    .data = .,
    `Total Weight`,
    `Sub Table` = NA
  )

final_result = bind_rows(
  result %>% filter(`Sub Table` == "Subtable-1"),
  subs %>% filter(`Sub Table` == "Subtable-1"),
  result %>% filter(`Sub Table` == "Subtable-2"),
  subs %>% filter(`Sub Table` == "Subtable-2"),
  result %>% filter(`Sub Table` == "Subtable-3"),
  subs %>% filter(`Sub Table` == "Subtable-3"),
  total
) %>%
  select(-`Sub Table`)

all.equal(final_result, test, check.attributes = FALSE)

  • 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
import numpy as np

path = "Power Query/300-399/347/PQ_Challenge_347 - Table Decomposition.xlsx"

input1 = pd.read_excel(path, sheet_name=0, usecols="A:F", nrows=5)
input2 = pd.read_excel(path, sheet_name=0, usecols="A:B", skiprows=7, nrows=4)
test = pd.read_excel(path, sheet_name=0, usecols="I:P", nrows=20).rename(columns=lambda col: col.replace(".1", ""))

input1["ID"] = ["ID" + str(i) for i in range(1, len(input1) + 1)]
cart = input1.merge(input2, how="cross")
cart["Activity ID"] = cart["Sub Table"] + "_" + cart["ID"]
cart["Quantity"] = cart["Quantity"] * cart["%"]
cart["Total Weight"] = cart["Quantity"] * cart["Unit Weight"]

cols = [
    "Sub Table", "Activity ID", "Activity Name", "Unit", "Quantity",
    "Unit Weight", "Total Weight", "Chainage", "Resource Name"
]
result = cart[cols].sort_values("Activity ID")

subs = result.groupby("Sub Table", as_index=False)["Total Weight"].sum()
subs["Activity ID"] = "TOTAL"
for col in ["Activity Name", "Unit", "Quantity", "Unit Weight", "Chainage", "Resource Name"]:
    subs[col] = None
subs = subs[cols]

total = pd.DataFrame({
    "Activity ID": ["GRAND TOTAL"],
    "Total Weight": [result["Total Weight"].sum()],
    **{col: [None] for col in cols if col not in ["Activity ID", "Total Weight"]}
})[cols]

final = pd.concat([
    result[result["Sub Table"] == "Subtable-1"],
    subs[subs["Sub Table"] == "Subtable-1"],
    result[result["Sub Table"] == "Subtable-2"],
    subs[subs["Sub Table"] == "Subtable-2"],
    result[result["Sub Table"] == "Subtable-3"],
    subs[subs["Sub Table"] == "Subtable-3"],
    total
], ignore_index=True)[cols[1:]]

final = final.replace({np.nan: "", None: ""})
test = test.replace({np.nan: "", None: ""})
print(test)
print(final)

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