Excel BI - PowerQuery Challenge 268

excel-challenges
power-query
Transpose
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 268

Challenge Description

Transpose

Solutions

library(tidyverse)
library(readxl)

path = "Power Query/PQ_Challenge_268.xlsx"
input = read_excel(path, range = "A1:B15")
test  = read_excel(path, range = "E1:J7") %>%
  replace(is.na(.), "")

result <- input %>% 
  pivot_longer(everything(), values_to = "value", names_to = NULL) %>% 
  drop_na() %>% 
  mutate(value1 = value) %>% 
  separate(value, into = c("name", "value"), sep = "(?<=\\D)(?=\\d)", remove = FALSE) %>% 
  pivot_wider(names_from = name, values_from = value1) %>% 
  select(-value) %>% 
  replace(is.na(.), "") %>% 
  select(sort(names(.)))

colnames(result) = colnames(test)

all.equal(result, test, check.attributes = FALSE)  
#> [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

path = "PQ_Challenge_268.xlsx"
input = pd.read_excel(path, usecols="A:B", nrows=15)
test = pd.read_excel(path, usecols="E:J", nrows=6).fillna("").rename(columns=lambda col: col.split('.')[0])

input['row'] = np.arange(len(input))
melted = input.melt(id_vars="row", value_name="value").dropna(subset=["value"])
melted["value1"] = melted["value"]
splits = melted["value"].str.extract(r"^([^\d]+)(.*)$")
melted["name"] = splits[0]
melted["value"] = splits[1]
result = melted.pivot_table(index="value", columns="name", values="value1", aggfunc="first")
result = result.reindex(sorted(result.columns), axis=1)
result = result.fillna("").reset_index(drop=True)

result.columns = test.columns

print(result.equals(test)) # True

  • Logic:

    • Reads the workbook range needed for the challenge

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

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