Excel BI - PowerQuery Challenge 278

excel-challenges
power-query
Transpose the table as shown where sum of amount shown is gross profit which is Revenue - Cost.
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 278

Challenge Description

Transpose the table as shown where sum of amount shown is gross profit which is Revenue - Cost.

Solutions

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

path = "Power Query/PQ_Challenge_278.xlsx"
input = read_excel(path, range = "A1:A10")
test  = read_excel(path, range = "C1:I5") %>%
  mutate(across(-c(1), ~replace_na(as.numeric(.x), 0)))

result = input %>%
  separate(col = 1, into = c("Date", "Org", "Revenue", "Cost"), sep = " - ", extra = "merge",convert = TRUE) %>%
  mutate(profit = Revenue - Cost,
         month = month(mdy(Date), label = TRUE, abbr = TRUE, locale = "en_US.UTF-8"),
         month = factor(month, ordered = T, levels = c("Jan", "Feb", "Mar", "Apr", "May", "Jun")
         )) %>%
  select(-c(Revenue, Cost, Date)) %>%
  arrange(Org) %>%
  pivot_wider(names_from = month, values_from = profit, values_fn = sum, names_expand = TRUE)

res = result %>%
  add_row(Org = "Total", 
          `Jan` = sum(result$Jan, na.rm = TRUE),
          `Feb` = sum(result$Feb, na.rm = TRUE),
          `Mar` = sum(result$Mar, na.rm = TRUE),
          `Apr` = sum(result$Apr, na.rm = TRUE),
          `May` = sum(result$May, na.rm = TRUE),
          `Jun` = sum(result$Jun, na.rm = TRUE)) %>%
  mutate(across(-Org, ~replace_na(.x, 0)))

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

    • 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 datetime import datetime

path = "PQ_Challenge_278.xlsx"

input_data = pd.read_excel(path, usecols="A", nrows=10)
test = pd.read_excel(path, usecols="C:I", nrows=4).fillna(0)
test = test.apply(lambda col: col.astype('int64') if col.dtypes == 'float64' else col)
input_data[['Date', 'Org', 'Revenue', 'Cost']] = input_data.iloc[:, 0].str.split(' - ', expand=True)
input_data['profit'] = pd.to_numeric(input_data['Revenue'], errors='coerce').fillna(0) - pd.to_numeric(input_data['Cost'], errors='coerce').fillna(0)
input_data['month'] = pd.to_datetime(input_data['Date'], errors='coerce').dt.strftime('%b').astype(pd.CategoricalDtype(categories=["Jan", "Feb", "Mar", "Apr", "May", "Jun"], ordered=True))
input_data.drop(columns=[input_data.columns[0], 'Revenue', 'Cost', 'Date'], inplace=True)

result = input_data.pivot_table(
    index='Org', columns='month', values='profit', aggfunc='sum', fill_value=0
).reset_index()

total_row = pd.DataFrame([{
    'Org': 'Total',
    **{month: result[month].sum() for month in ["Jan", "Feb", "Mar", "Apr", "May", "Jun"]}
}])
res = pd.concat([result, total_row], ignore_index=True).fillna(0)


print(res.equals(test))

  • Logic:

    • Reads the workbook range needed for the challenge

    • Reshapes the data into the structure required by the result table

    • Applies the rule iteratively until the output is complete

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