Excel BI - PowerQuery Challenge 274

excel-challenges
power-query
Dish Ingredient Inventory A p1 p3
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 274

Challenge Description

Dish Ingredient Inventory A p1 p3

Solutions

library(tidyverse)
library(readxl)
library(R6)

path = "Power Query/PQ_Challenge_274.xlsx"
input1 = read_excel(path, range = "A1:B12") %>% 
  rename("order_id" = 1, "product" = 2)
input2 = read_excel(path, range = "D1:E6") %>%
  rename("product" = 1, "quantity" = 2)
test  = read_excel(path, range = "D10:E14")

OrderFulfiller <- R6Class(
  "OrderFulfiller",
  public = list(
    inventory = NULL,
    initialize = function(inventory_df) {
      self$inventory <- inventory_df %>%
        mutate(remaining = quantity)
    },
    
    fulfill_orders = function(orders_df) {
      orders_df %>%
        mutate(row_id = row_number()) %>%
        rowwise() %>%
        mutate(
          quantity = self$fulfill_single(product)
        ) %>%
        ungroup() %>%
        select(-row_id)
    },
    
    fulfill_single = function(product_name) {
      idx <- which(self$inventory$product == product_name)
      if (length(idx) == 0) {
        return(0L)
      }
      if (self$inventory$remaining[idx] > 0) {
        self$inventory$remaining[idx] <- self$inventory$remaining[idx] - 1
        return(1L)
      } else {
        return(0L)
      }
    }
  )
)
fulfiller <- OrderFulfiller$new(input2)
fulfilled_orders <- fulfiller$fulfill_orders(input1) 

result = fulfilled_orders %>%
  filter(quantity > 0) %>%
  summarise(Ingredient = paste(sort(product), collapse = ", "), .by = order_id) %>%
  rename("Dish" = order_id)

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

  • 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_274.xlsx"
input1 = pd.read_excel(path, usecols="A:B", nrows=12, names=["order_id", "product"])
input2 = pd.read_excel(path, usecols="D:E", nrows=6, names=["product", "quantity"])
test = pd.read_excel(path, usecols="D:E", skiprows=9, nrows=5)

class OrderFulfiller:
    def __init__(self, inventory_df):
        self.inventory = inventory_df.copy()
        self.inventory["remaining"] = self.inventory["quantity"]

    def fulfill_orders(self, orders_df):
        orders_df["quantity"] = orders_df["product"].apply(self.fulfill_single)
        return orders_df

    def fulfill_single(self, product_name):
        idx = self.inventory[self.inventory["product"] == product_name].index
        if not idx.empty and self.inventory.at[idx[0], "remaining"] > 0:
            self.inventory.at[idx[0], "remaining"] -= 1
            return 1
        return 0

fulfiller = OrderFulfiller(input2)
fulfilled_orders = fulfiller.fulfill_orders(input1)

result = (
    fulfilled_orders[fulfilled_orders["quantity"] > 0]
    .groupby("order_id")["product"]
    .apply(lambda x: ", ".join(sorted(x)))
    .reset_index(name="Ingredient")
    .rename(columns={"order_id": "Dish"})
)

print(result.equals(test))

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