Excel BI - PowerQuery Challenge 363

excel-challenges
power-query
Category Item Price Order ID Order String (Qty x Item) Discount Code
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 363

Challenge Description

Category Item Price Order ID Order String (Qty x Item) Discount Code

Solutions

library(tidyverse)
library(readxl)

path <- "Power Query/300-399/363/PQ_Challenge_363.xlsx"
input1 <- read_excel(path, range = "A1:C16")
input2 <- read_excel(path, range = "E1:G11")
test <- read_excel(path, range = "E16:F26")

res = input2 %>%
  separate_longer_delim(`Order String (Qty x Item)`, delim = ", ") %>%
  separate(`Order String (Qty x Item)`, into = c("Qty", "Item"), sep = "x") %>%
  left_join(input1, by = "Item") %>%
  mutate(Qty = as.numeric(Qty)) %>%
  mutate(
    Total = case_when(
      `Discount Code` == "NONE" ~ Price * Qty,
      `Discount Code` == "SAVE10" ~ (Price * Qty) * 0.9,
      `Discount Code` == "BOGO-DRINK" & Category == "Drink" ~ Price *
        ((Qty + 1) %/% 2),
      TRUE ~ Price * Qty
    )
  ) %>%
  summarise(`Final Total` = sum(Total), .by = `Order ID`)

all.equal(res, test)
#> [1] TRUE

  • 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
import numpy as np
from numpy.testing import assert_almost_equal

path = "Power Query/300-399/363/PQ_Challenge_363.xlsx"

input1 = pd.read_excel(path, usecols="A:C", nrows=16)
input2 = pd.read_excel(path, usecols="E:G", nrows=11)
test = pd.read_excel(path, usecols="E:F", skiprows=15, nrows=11)

input2 = input2.assign(order_item=input2['Order String (Qty x Item)'].str.split(', ')).explode('order_item')
input2[['Qty', 'Item']] = input2['order_item'].str.split('x', expand=True)
input2['Qty'] = pd.to_numeric(input2['Qty'].str.strip())
input2['Item'] = input2['Item'].str.strip()
input2 = input2.merge(input1, on='Item', how='left')

input2['Total'] = np.select(
    [
        input2['Discount Code'] == "NONE",
        input2['Discount Code'] == "SAVE10",
        (input2['Discount Code'] == "BOGO-DRINK") & (input2['Category'] == "Drink")
    ],
    [
        input2['Price'] * input2['Qty'],
        input2['Price'] * input2['Qty'] * 0.9,
        input2['Price'] * ((input2['Qty'] + 1) // 2)
    ],
    default=input2['Price'] * input2['Qty']
)

res = input2.groupby('Order ID', as_index=False).agg({'Total': 'sum'}).rename(columns={'Total': 'Final Total'})

# Check if 'res' is almost equal to 'test'
try:
    assert_almost_equal(res['Final Total'].values, test['Final Total'].values, decimal=2)
    print("res is almost equal to test")
except AssertionError:
    print("res is not almost equal to test")

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