Excel BI - PowerQuery Challenge 338

excel-challenges
power-query
Transpose the table as shown.
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 338

Challenge Description

Transpose the table as shown.

Solutions

library(tidyverse)
library(readxl)

excel_path <- "Power Query/300-399/338/PQ_Challenge_338.xlsx"
input = read_excel(excel_path, range = "A1:F17")
test  = read_excel(excel_path, range = "H1:L21")

result = input %>%
  mutate(store = if_else(str_detect(Column1, "Store"), Column1, NA_character_)) %>%
  fill(store) %>%
  mutate(item = if_else(str_detect(Column1, "Item"), Column1, NA_character_))

m1 = result %>%
  filter(Column2 %in% c("M", "F")) %>%
  select(-c(Column1, item)) %>%
  rename(gender = Column2, Q1 = Column3, Q2 = Column4, Q3 = Column5, Q4 = Column6, Store = store) %>%
  pivot_longer(starts_with("Q"), names_to = "Quarter", values_to = "Sales") %>%
  group_by(Store, Quarter) %>%
  summarise(`Total Employees` = sum(as.numeric(Sales)), .groups = "drop")

m2 = result %>%
  filter(!Column2 %in% c("M", "F")) %>%
  fill(Column1) %>%
  filter(!str_detect(Column1, "Store")) %>%
  select(-item, Item = Column1, Measure = Column2, Q1 = Column3, Q2 = Column4, 
         Q3 = Column5, Q4 = Column6, Store = store) %>%
  pivot_longer(starts_with("Q"), names_to = "Quarter", values_to = "Value") %>%
  pivot_wider(names_from = Measure, values_from = Value) %>%
  mutate(Amount = as.numeric(Quantity) * as.numeric(Price)) %>%
  select(-Quantity, -Price)

final = left_join(m1, m2, by = c("Store", "Quarter")) %>%
  select(Store, Quarter, `Total Employees`, Item, Amount) %>%
  arrange(Store, Item, Quarter)

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

path = "Power Query/300-399/338/PQ_Challenge_338.xlsx"
input = pd.read_excel(path, usecols="A:F", nrows=17)
test = pd.read_excel(path, usecols="H:L", nrows=21)

input['store'] = np.where(input['Column1'].str.contains("Store", na=False), input['Column1'], np.nan)
input['store'] = input['store'].ffill()

m1 = (
    input[input['Column2'].isin(['M', 'F'])]
    .rename(columns={'Column2': 'gender', 'Column3': 'Q1', 'Column4': 'Q2', 'Column5': 'Q3', 'Column6': 'Q4'})
    .melt(id_vars=['store', 'gender'], value_vars=['Q1', 'Q2', 'Q3', 'Q4'], var_name='Quarter', value_name='Sales')
    .groupby(['store', 'Quarter'], as_index=False)
    .agg({'Sales': lambda x: pd.to_numeric(x, errors='coerce').sum()})
    .rename(columns={'store': 'Store', 'Sales': 'Total Employees'})
)

non_emp = input[~input['Column2'].isin(['M', 'F'])].copy()
non_emp['Column1'] = non_emp['Column1'].ffill()
non_emp = non_emp[~non_emp['Column1'].str.contains("Store", na=False)]

m2 = (
    non_emp.rename(columns={'Column1': 'Item', 'Column2': 'Measure', 'Column3': 'Q1', 'Column4': 'Q2', 'Column5': 'Q3', 'Column6': 'Q4'})
    .melt(id_vars=['store', 'Item', 'Measure'], value_vars=['Q1', 'Q2', 'Q3', 'Q4'], var_name='Quarter', value_name='Value')
    .pivot(index=['store', 'Item', 'Quarter'], columns='Measure', values='Value')
    .reset_index()
)
m2['Amount'] = pd.to_numeric(m2['Quantity'], errors='coerce') * pd.to_numeric(m2['Price'], errors='coerce')
m2 = m2.rename(columns={'store': 'Store'})

final = (
    m1.merge(m2[['Store', 'Item', 'Quarter', 'Amount']], on=['Store', 'Quarter'], how='left')
    .loc[:, ['Store', 'Quarter', 'Total Employees', 'Item', 'Amount']]
    .sort_values(['Store', 'Item', 'Quarter'])
    .reset_index(drop=True)
)
print(final.equals(test))

  • 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

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