Excel BI - PowerQuery Challenge 263

excel-challenges
power-query
Store Responses Green Yellow Red C
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 263

Challenge Description

Store Responses Green Yellow Red C

Solutions

library(tidyverse)
library(readxl)

path = "Power Query/PQ_Challenge_263.xlsx"
input1 = read_excel(path, range = "A1:B132")
input2 = read_excel(path, range = "D1:F7")
test  = read_excel(path, range = "D12:E16")

result = input2 %>%
  pivot_longer(everything(), names_to = "Attitude", values_to = "Response", values_drop_na = TRUE) %>%
  left_join(input1, by = c("Response" = "Responses")) %>%
  summarise(count = n(), .by = c(Store, Attitude)) %>%
  mutate(Rank = dense_rank(desc(count)), .by = Attitude) %>%
  filter(Attitude == "Green" ) %>%
  select(Store, Rank)

  • 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 = "PQ_Challenge_263.xlsx"
input1 = pd.read_excel(path, sheet_name=0, usecols="A:B", nrows=131)
input2 = pd.read_excel(path, sheet_name=0, usecols="D:F", nrows=6)
test = pd.read_excel(path, sheet_name=0, usecols="D:E", skiprows=11, nrows=5)

input2_long = input2.melt(var_name="Attitude", value_name="Response").dropna()

result = input2_long.merge(input1, left_on="Response", right_on="Responses", how="left")
result = result.groupby(['Store', 'Attitude']).size().reset_index(name='count')
result['Rank'] = result.groupby('Attitude')['count'].rank(method='dense', ascending=False)
result = result[result['Attitude'] == 'Green'][['Store', 'Rank']].sort_values(by = "Rank").reset_index(drop=True)

print(result)

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