Excel BI - PowerQuery Challenge 332

excel-challenges
power-query
Sum Of qty_sold Sum Of Sum Of qty_sold
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 332

Challenge Description

Sum Of qty_sold Sum Of Sum Of qty_sold

Solutions

library(tidyverse)
library(readxl)

path = "Power Query/300-399/332/PQ_Challenge_332.xlsx"
input = read_excel(path, range = "A1:L22776")

result = input %>%
  mutate(monthyear = floor_date(date, "month")) %>%
  group_by(Category, city_name, monthyear) %>%
  summarise(sum_of_mrp = sum(MRP, na.rm = TRUE),
            sum_of_quantity = sum(qty_sold, na.rm = TRUE)) %>%
  arrange(Category, city_name, monthyear) %>%
  ungroup() %>%
  group_by(Category, city_name) %>%
  mutate(mrp_m2m_perc = (sum_of_mrp - lag(sum_of_mrp)) / lag(sum_of_mrp),
         quantity_m2m_perc = (sum_of_quantity - lag(sum_of_quantity)) / lag(sum_of_quantity)) %>%
  ungroup() %>%
  pivot_wider(names_from = monthyear, 
              values_from = c(sum_of_mrp, sum_of_quantity,mrp_m2m_perc,quantity_m2m_perc),
              names_glue = "{monthyear}_{.value}") %>%
  select(Category, city_name, contains("09"), contains("08"), contains("07"), contains("06"))

  • 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
from pprint import pprint

path = "300-399/332/PQ_Challenge_332.xlsx"
input = pd.read_excel(path, usecols="A:L", nrows=22776)

input['date'] = pd.to_datetime(input['date'])
input['my'] = input['date'].dt.to_period('M').dt.to_timestamp()

res = (
    input
    .groupby(['Category', 'city_name', 'my'], as_index=False)
    .agg(mrp=('MRP', 'sum'), qty=('qty_sold', 'sum'))
    .sort_values(['Category', 'city_name', 'my'])
)
res['mrp_pct'] = res.groupby(['Category', 'city_name'])['mrp'].pct_change()
res['qty_pct'] = res.groupby(['Category', 'city_name'])['qty'].pct_change()
piv = res.pivot(index=['Category', 'city_name'], columns='my', values=['mrp', 'qty', 'mrp_pct', 'qty_pct'])
piv.columns = [f"{col[1].strftime('%m_%Y')}_{col[0]}" for col in piv.columns]
piv = piv.reset_index()

months = ["09", "08", "07", "06"]
cols = ['Category', 'city_name']
vals = ['mrp', 'qty', 'mrp_pct', 'qty_pct']

for m in months:
    for v in vals:
        cols += [c for c in piv.columns if c.startswith(f"{m}_") and c.endswith(f"_{v}")]
final_result = piv[cols]
pprint(final_result)
print(final_result.columns.tolist())

  • 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

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