Excel BI - PowerQuery Challenge 233

Calculate the Shipped and Non-shipped amounts (Quantity * Price) for all Fruits.

Illustration for Excel BI - PowerQuery Challenge 233

Challenge Description

Calculate the Shipped and Non-shipped amounts (Quantity * Price) for all Fruits.

Solutions

R Solution

library(tidyverse)
library(readxl)
library(janitor)

path = "Power Query/PQ_Challenge_233.xlsx"
input = read_excel(path, range = "A1:G7")
test  = read_excel(path, range = "A13:H16")

result = input %>%
  pivot_longer(-c(1), names_to = c(".value", "number"), names_pattern = "([A-Za-z]+)(\\d)") %>%
  mutate(amount = Price * Quantity) %>%
  summarise(amount = sum(amount), .by = c("Fruits", "Shipped")) %>% 
  mutate(Status = recode(Shipped, "Y" = "Shipped Amount", "N" = "Not Shipped Amount")) %>%
  select(-Shipped) %>%
  pivot_wider(names_from = Fruits, values_from = amount, values_fill = 0) %>%
  adorn_totals("both") %>%
  select(Status, Apple, Banana, Papaya, Mango, Pineapple, Kiwi, Total)

all.equal(result, test, check.attributes = FALSE)
#> [1] TRUE

R Analysis

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

Python Solution

import pandas as pd

path = "PQ_Challenge_233.xlsx"
input = pd.read_excel(path, usecols="A:G", nrows=7)
test = pd.read_excel(path, usecols="A:H", skiprows=12, nrows=4)

input_long = input.melt(id_vars=['Fruits'], var_name='variable', value_name='value')
input_long[['Type', 'number']] = input_long['variable'].str.extract(r'([A-Za-z]+)(\d)')
input_long = input_long.pivot(index=['Fruits', 'number'], columns='Type', values='value').reset_index()

input_long['amount'] = input_long['Price'] * input_long['Quantity']
result = input_long.groupby(['Fruits', 'Shipped'], as_index=False).agg(amount=('amount', 'sum'))
result['Status'] = result['Shipped'].replace({'Y': 'Shipped Amount', 'N': 'Not Shipped Amount'})

result_pivot = result.pivot_table(index='Status', columns='Fruits', values='amount', aggfunc="sum", fill_value=0, margins=True, margins_name='Total').reset_index()
result_pivot = result_pivot[result_pivot['Status'] != 'Total'].sort_values(by='Status', ascending=False)\
        ._append(result_pivot[result_pivot['Status'] == 'Total'], ignore_index=True)

result_pivot = result_pivot[['Status', 'Apple', 'Banana', 'Papaya', "Mango", "Pineapple", "Kiwi", 'Total']]
result_pivot.columns.name = None

print(all(result_pivot == test))  # True

Python

• 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

  • Uses direct pattern parsing where the workbook encodes logic in text

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

Related Challenges

Explore more challenges from the same series:

• Excel BI - PowerQuery Challenge 232