Excel BI - PowerQuery Challenge 284

excel-challenges
power-query
Sum of Apple Values given are in the range of 1 to 100. Sum the values over 5 different equal sized bins.
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 284

Challenge Description

Sum of Apple Values given are in the range of 1 to 100. Sum the values over 5 different equal sized bins.

Solutions

library(tidyverse)
library(readxl)

path = "Power Query/200-299/284/PQ_Challenge_284.xlsx"
input = read_excel(path, range = "A1:D20")
test = read_excel(path, range = "F1:J6")

result = input %>%
  pivot_longer(everything(), names_to = "name", values_to = "value") %>%
  mutate(
    bin = cut(value, breaks = seq(0, 100, by = 20), include.lowest = TRUE)
  ) %>%
  pivot_wider(
    names_from = name,
    values_from = value,
    values_fn = list(value = sum)
  ) %>%
  arrange(bin)

  • Logic:

    • Reads the workbook range needed for the challenge

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

    • 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 = "200-299/284/PQ_Challenge_284.xlsx"

input = pd.read_excel(path, usecols="A:D", nrows=20)
test = pd.read_excel(path, usecols="F:J", nrows=6)

result = (input.melt(var_name="name", value_name="value")
          .assign(bin=lambda df: pd.cut(df['value'], bins=range(0, 101, 20), include_lowest=True))
          .pivot_table(index="bin", columns="name", values="value", aggfunc="sum")
          .reset_index()
          .sort_values(by="bin")
          .reset_index(drop=True)
          .pipe(lambda df: df[['bin'] + sorted(df.columns.difference(['bin']), reverse=True)]))


print(result)

  • Logic:

    • Reads the workbook range needed for the challenge

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

    • Builds helper columns that drive the final output

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