Excel BI - PowerQuery Challenge 315

excel-challenges
power-query
Transpose the table as shown. Turnover column in Result is Turnover divided by number of Sold Items.
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 315

Challenge Description

Transpose the table as shown. Turnover column in Result is Turnover divided by number of Sold Items.

Solutions

library(tidyverse)
library(readxl)

path = "Power Query/300-399/315/PQ_Challenge_315.xlsx"
input = read_excel(path, range = "A1:D11")
test  = read_excel(path, range = "F1:J12")

result = input %>% 
  pivot_longer(cols = everything(), names_to = "Attribute", values_to = "Value" ) %>%
  arrange(Attribute) %>%
  mutate(r = (row_number() + 1) %% 2,
         nr = (row_number() + 1) %/% 2) %>%
  pivot_wider(id_cols = c(Attribute, nr), names_from = r, values_from = Value) %>%
  select(-nr) %>%
  pivot_wider(names_from  = `0`, values_from = `1`) %>%
  separate_longer_delim(cols = `Sold Items`, delim = " | ") %>%
  mutate(across(c(Turnover, `Sold Items`, Age), as.numeric)) %>%
  mutate(Turnover = Turnover / n(), .by = Attribute) %>%
  select(-Attribute) 

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

path = "300-399/315/PQ_Challenge_315.xlsx"
input = pd.read_excel(path, sheet_name=0, usecols="A:D", nrows=10)
test = pd.read_excel(path, sheet_name=0, usecols="F:J", nrows=12)

df = input.melt(var_name="Attribute", value_name="Value")
df["r"] = "n" + (df.index % 2).astype(str)
df["nr"] = df.groupby("Attribute").cumcount() // 2
df = df.pivot(index=["Attribute", "nr"], columns="r", values="Value").reset_index()
df = df.drop(columns="nr")
df = df.pivot(index="Attribute", columns="n0", values="n1").reset_index(drop=True)
df['Sold Items'] = df['Sold Items'].astype(str).str.split(' \| ')
df = df.explode('Sold Items').reset_index(drop=True)
for col in ['Turnover', 'Age', 'Sold Items']:
    df[col] = pd.to_numeric(df[col], errors='coerce')
df['Turnover'] = df.groupby('Incharge')['Turnover'].transform(lambda x: x / len(x)).astype(int)
result = df[["Month", "Incharge", "Age", "Sold Items", "Turnover"]]
result.columns.name = None

print(result.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

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