Excel BI - PowerQuery Challenge 275

excel-challenges
power-query
Group Groups Transpose the problem table into result table where value is sum of Values from problem table.
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 275

Challenge Description

Group Groups Transpose the problem table into result table where value is sum of Values from problem table.

Solutions

library(tidyverse)
library(readxl)

path = "Power Query/PQ_Challenge_275.xlsx"
input = read_excel(path, range = "A1:E7")
test  = read_excel(path, range = "G1:I7")

result = input %>%
  pivot_longer(
    cols = -Group,
    names_to = c(".value", "index"),
    names_pattern = "([A-Za-z]+)(\\d)"
  ) %>%
  select(-index) %>%
  summarise(Value = sum(Value), 
            Groups = paste0(Group, collapse = ", "), .by = Number) %>%
  arrange(Number) 


# GH09 should have two groupss in answeer

  • 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 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 = "PQ_Challenge_275.xlsx"

input = pd.read_excel(path, usecols="A:E", nrows=7)
test = pd.read_excel(path, usecols="G:I", nrows=7)

input["id"] = input.index
result = pd.wide_to_long(input, stubnames=['Number', 'Value'], i=['id', 'Group'], j='index', sep='', suffix='\\d+').reset_index()
result = result.drop(columns=['id']).groupby('Number').agg(
    Value=('Value', 'sum'),
    Group=('Group', lambda x: ','.join(x.unique()))
).sort_values('Number').reset_index()

  • Logic:

    • Reads the workbook range needed for the challenge

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