Excel BI - PowerQuery Challenge 337

excel-challenges
power-query
Pivot the given table where amount = Price * Nos. Header orders should be S, M & L. Item column order should be ascending.
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 337

Challenge Description

Pivot the given table where amount = Price * Nos. Header orders should be S, M & L. Item column order should be ascending.

Solutions

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

path = "Power Query/300-399/337/PQ_Challenge_337.xlsx"
input = read_excel(path, range = "A1:A106")
test  = read_excel(path, range = "D2:H7")

item_order = c("Shirt", "Shorts", "Trouser", "T Shirt")
size_order = c("S", "M", "L")

result = input %>%
  mutate(
    Item = str_extract(Data, ".*(?= Size)"),
    Size = str_extract(Data, "(?<=Size )(.*?)(?= Price)") %>% str_sub(.,1,1),
    Price = str_extract(Data, "(?<=Price )(.*?)(?= Nos)") %>% as.numeric(),
    Nos = str_extract(Data, "(?<=Nos ).*") %>% as.numeric()
  ) %>%
  summarise(Total_Price = sum(Price * Nos), .by = c(Item, Size)) %>%
  mutate(
    Item = factor(Item, levels = item_order),
    Size = factor(Size, levels = size_order)
  ) %>%
  arrange(Item, Size) %>%
  pivot_wider(
    names_from = Size,
    values_from = Total_Price,
    names_sort = FALSE
  ) %>%
  adorn_totals(where = c("row", "col"), name = "Grand Total") %>%
  mutate(Item = as.character(Item))

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

  • Logic:

    • Reads the workbook range needed for the challenge

    • Reshapes the data into the structure required by the result table

    • 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 = "Power Query/300-399/337/PQ_Challenge_337.xlsx"
item_order = ["Shirt", "Shorts", "Trouser", "T Shirt"]
size_order = ["S", "M", "L"]
test = pd.read_excel(path, usecols="D:H", nrows=5, skiprows=1)

pat = r"^(.*?) Size (\w).*?Price ([\d.]+).*?Nos (\d+)"
df = pd.read_excel(path, usecols="A", nrows=106)["Data"].str.extract(pat)
df.columns = ["Item", "Size", "Price", "Nos"]
df["Price_per_Nos"] = df["Price"].astype(float) * df["Nos"].astype(int)

pivot = pd.pivot_table(
    df, values="Price_per_Nos", index="Item", columns="Size", aggfunc="sum", fill_value=0
).reindex(index=item_order, columns=size_order).reset_index()

pivot["Grand Total"] = pivot[size_order].sum(axis=1)
totals = pivot[size_order + ["Grand Total"]].sum()
pivot.loc[len(pivot)] = ["Grand Total"] + totals.tolist()
pivot[size_order + ["Grand Total"]] = pivot[size_order + ["Grand Total"]].round().astype(int)

print(pivot.equals(test))

  • Logic:

    • Reads the workbook range needed for the challenge

    • Reshapes the data into the structure required by the result table

    • Uses direct pattern parsing where the workbook encodes logic in text

  • 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 moderate:

  • It combines reshaping, grouping, or parsing steps that are common in Power Query style problems.

  • The main challenge is reproducing the workbook output structure exactly.