Excel BI - PowerQuery Challenge 230

excel-challenges
power-query
Summarize the table as shown by summing up Sales against each month.
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 230

Challenge Description

Summarize the table as shown by summing up Sales against each month.

Solutions

library(tidyverse)
library(readxl)

path = "Power Query/PQ_Challenge_230.xlsx"
input = read_excel(path, range = "A1:H17")
test  = read_excel(path, range = "J1:K13")

df = input %>% 
  split.default(., ceiling(seq_along(.) / 2)) %>%
  map_dfr(~ .x, .id = NULL) %>%
  mutate(Week = Month) %>%
  mutate(Month = ifelse(is.na(Sale), Week, NA_character_)) %>%
  fill(Month) %>%
  summarise(Sale = sum(Sale, na.rm = TRUE), .by = Month)

all.equal(df, test, check.attributes = FALSE)
#> [1] TRUE

  • Logic:

    • Reads the workbook range needed for the challenge

    • 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 numpy as np

path = "PQ_Challenge_230.xlsx"
input = pd.read_excel(path, usecols="A:H", nrows=17).rename(columns=lambda x: x.split('.')[0])
test = pd.read_excel(path, usecols="J:K", nrows=12).rename(columns=lambda x: x.split('.')[0])

splits = [input.iloc[:, i:i+2] for i in range(0, input.shape[1], 2)]

df = pd.concat(splits, axis=0, ignore_index=True)

df['Week'] = df['Month']
df['Month'] = df.apply(lambda row: row['Week'] if pd.isna(row['Sale']) else np.NaN, axis=1)
df['Month'] = df['Month'].ffill()
summary = df.groupby('Month', as_index=False)['Sale'].sum()

check = test.merge(summary, on='Month', how='left')
check['Check'] = check['Sale_x'] == check['Sale_y']
print(all(check['Check'])) # True

  • Logic:

    • Reads the workbook range needed for the challenge

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

  • The transformation rule is readable, but the final layout still requires a careful implementation.