Excel BI - PowerQuery Challenge 355

excel-challenges
power-query
OrderID Product Region Quantity UnitPrice IsReturn
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 355

Challenge Description

OrderID Product Region Quantity UnitPrice IsReturn

Solutions

library(tidyverse)
library(readxl)

path <- "Power Query/300-399/355/PQ_Challenge_355.xlsx"
input <- read_excel(path, range = "A1:F51")
test <- read_excel(path, range = "H1:K5")

result = input %>%
  transmute(
    Region = Product,
    Quantity = ifelse(!IsReturn, Quantity, -Quantity),
    UnitPrice = UnitPrice
  ) %>%
  summarise(
    `Total Quantity` = sum(Quantity),
    `Total Amount` = sum(Quantity * UnitPrice),
    .by = Region
  ) %>%
  janitor::adorn_totals(where = "row", name = "Total") %>%
  mutate(`Average Price` = round(`Total Amount` / `Total Quantity`, 2))

all.equal(result, 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

path = "Power Query/300-399/355/PQ_Challenge_355.xlsx"
input = pd.read_excel(path, usecols="A:F", nrows=50)
test = pd.read_excel(path, usecols="H:K", nrows=4).rename(columns=lambda col: col.replace('.1', ''))

input['Quantity'] = input.apply(lambda x: x['Quantity'] if not x['IsReturn'] else -x['Quantity'], axis=1)
input['Region'] = input['Product']

result = (
    input.groupby('Region', as_index=False)
    .agg(
        **{
            'Total Quantity': ('Quantity', 'sum'),
        }
    )
)
result['Total Amount'] = (
    input.groupby('Region')
    .apply(lambda x: (x['Quantity'] * x['UnitPrice']).sum())
    .values
)
totals = pd.DataFrame({
    'Region': ['Total'],
    'Total Quantity': [result['Total Quantity'].sum()],
    'Total Amount': [result['Total Amount'].sum()]
})
result = pd.concat([result, totals], ignore_index=True)
result['Average Price'] = (result['Total Amount'] / result['Total Quantity']).round(2)

print(result.equals(test))
# Output: True

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

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