Excel BI - PowerQuery Challenge 203

excel-challenges
power-query
Group Group1 Group2 Group3 Prepare the groups which lie between two blank Amount1s and give the average of numbers appearing in those groups (groups shaded in light red).
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 203

Challenge Description

Group Group1 Group2 Group3 Prepare the groups which lie between two blank Amount1s and give the average of numbers appearing in those groups (groups shaded in light red).

Solutions

library(tidyverse)
library(readxl)

path = "Power Query/PQ_Challenge_203.xlsx"
input = read_excel(path, range = "A1:C14")
test  = read_excel(path, range = "E1:F5")

result = input %>%
  mutate(Text = as.numeric(Text),
         Group = consecutive_id(is.na(Amount1)) / 2 * !is.na(Amount1)) %>%
  mutate(Group = ifelse(is.na(Amount1), "Remaining", paste0("Group", Group))) %>%
  summarise(nmb = list(c(Amount1, Amount2, Text)), .by = Group) %>%
  mutate(nmb = map(nmb, ~.x[!is.na(.x)])) %>%
  mutate(avg = map_dbl(nmb, ~mean(.x, na.rm = TRUE)) %>% round()) %>%
  arrange(Group) %>%
  select(Group, `Avg Amount` = avg)

identical(result, test)
# [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_203.xlsx"
input = pd.read_excel(path, usecols="A:C")
test = pd.read_excel(path, usecols="E:F", nrows=4)

result = input.assign(Text=pd.to_numeric(input["Text"], errors='coerce'),
                      Group=(input["Amount1"].isna().cumsum() * ~input["Amount1"].isna())
                             .astype(int)
                             .replace(0, "Remaining"))\
                .groupby("Group") \
                .agg(lambda x: x.values.tolist())\
                .assign(nmb=lambda x: x["Text"] + x["Amount1"] + x["Amount2"])

result["nmb"] = result["nmb"].apply(lambda y: [i for i in y if not np.isnan(i)])
result["avg"] = result["nmb"].apply(lambda y: round(np.mean(y), 0)).astype("int64")

result = result.drop(columns=["Text", "Amount1", "Amount2"])\
                .rename(columns={"avg": "Avg Amount"})\
                .drop(columns="nmb")\
                .reset_index(drop=False)\
                .assign(Group=lambda x: np.select([x["Group"] == 1, x["Group"] == 2, x["Group"] == 4],
                                                 ["Group1", "Group2", "Group3"],
                                                 default=x["Group"]))

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

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