Excel BI - PowerQuery Challenge 333

excel-challenges
power-query
Pivot the alphabets and sum of values associated with them.
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 333

Challenge Description

Pivot the alphabets and sum of values associated with them.

Solutions

library(tidyverse)
library(readxl)

path = "Power Query/300-399/333/PQ_Challenge_333.xlsx"

input =  read_excel(path, range = "A1:A4")
test = read_excel(path, range = "C1:D5")

result = input %>%
  separate_longer_delim(Data, regex("[, ]+")) %>%
  separate_wider_regex(Data, c(Alphabets = "[A-Z]?", Value = ".*")) %>%
  mutate(Value = as.numeric(Value), 
         Alphabets = na_if(Alphabets, "")) %>%
  fill(Alphabets, .direction = "down") %>%
  summarise(Value = sum(Value), .by = Alphabets) %>%
  arrange(Alphabets) %>%
  bind_rows(summarise(., Alphabets = "Total", Value = sum(Value)))

all.equal(result, test)
# [1] TRUE

  • 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
import re

path = "300-399/333/PQ_Challenge_333.xlsx"

input = pd.read_excel(path, usecols="A", nrows=4)
test = pd.read_excel(path, usecols="C:D", nrows=5)

data = " ".join(input["Data"].dropna())
items = re.split(r"[, ]+", data)

df = pd.DataFrame([
    (item[0] if item[0].isalpha() else None, int(item[1:] if item[0].isalpha() else item))
    for item in items if item
], columns=["Alphabets", "Value"])

df["Alphabets"] = df["Alphabets"].ffill()

result = df.groupby("Alphabets", as_index=False)["Value"].sum().sort_values("Alphabets")
result = pd.concat([result, pd.DataFrame([["Total", result["Value"].sum()]], columns=result.columns)]).reset_index(drop=True)  

print(result.equals(test)) #True

  • Logic:

    • Reads the workbook range needed for the challenge

    • Aggregates or ranks values at the relevant grouping level

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

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