Excel BI - PowerQuery Challenge 296

excel-challenges
power-query
Column1 Column2 Column3 Column4 Fruits Sales
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 296

Challenge Description

Column1 Column2 Column3 Column4 Fruits Sales

Solutions

library(tidyverse)
library(readxl)

path = "200-299/296/PQ_Challenge_296.xlsx"
input = read_excel(path, range = "A1:D13")
test = read_excel(path, range = "G1:H8")

result = input %>%
  mutate(row = row_number(), row_oddity = (row + 1) %% 2) %>%
  pivot_longer(
    cols = -c(row, row_oddity),
    names_to = "col",
    values_to = "value"
  )

result2 = bind_cols(
  result %>% filter(row_oddity == 0) %>% select(-row_oddity),
  result %>% filter(row_oddity == 1) %>% select(-row_oddity, value2 = value)
) %>%
  select(Fruits = value, value2) %>%
  mutate(value2 = as.numeric(value2)) %>%
  filter(!is.na(value2)) %>%
  summarise(Sales = sum(value2), .by = Fruits) %>%
  arrange(Fruits) %>%
  add_row(Fruits = "Total", Sales = sum(.$Sales))

all.equal(result2, 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 = "200-299/296/PQ_Challenge_296.xlsx"
input = pd.read_excel(path, usecols="A:D", nrows=13)
test = pd.read_excel(path, usecols="G:H", nrows=7)

input = input.copy()
input['row'] = range(1, len(input) + 1)
input['row_oddity'] = (input['row'] + 1) % 2

df = input.melt(id_vars=['row', 'row_oddity'])
even = df[df['row_oddity'] == 0].reset_index(drop=True)
odd = df[df['row_oddity'] == 1]['value'].reset_index(drop=True)
result = pd.DataFrame({'Fruits': even['value'], 'Sales': pd.to_numeric(odd, errors='coerce')}).dropna()
summary = result.groupby('Fruits', as_index=False)['Sales'].sum().astype({'Sales': int}).sort_values('Fruits')
final_result = pd.concat([summary, pd.DataFrame([{'Fruits': 'Total', 'Sales': summary['Sales'].sum()}])], ignore_index=True)

print(final_result.equals(test)) ## 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

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