Excel BI - PowerQuery Challenge 164

excel-challenges
power-query
Group Transpose the problem table into result table.
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 164

Challenge Description

Group Transpose the problem table into result table.

Solutions

library(tidyverse)
library(readxl)

input = read_excel("Power Query/PQ_Challenge_164.xlsx", range = "A1:E7")
test  = read_excel("Power Query/PQ_Challenge_164.xlsx", range = "G1:J13")

result = input %>%
  pivot_longer(cols = -c(1), 
               names_to = c(".value", "suffix"), 
               names_pattern = "(\\D+)(\\d+)") %>%
  mutate(Type = str_extract_all(Number, "[A-Z]+") %>% map_chr(~paste(., collapse = "")),
         Code = str_extract_all(Number, "\\d+") %>% map_chr(~paste(., collapse = ""))) %>%
  select(Group, Type, Code, Value) 

identical(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

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

  • 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

input_data = pd.read_excel("PQ_Challenge_164.xlsx", usecols="A:E", nrows=7)
test = pd.read_excel("PQ_Challenge_164.xlsx", usecols="G:J", nrows=13)

result = input_data.melt(id_vars="Group", var_name="name", value_name="Value")
parts = result["name"].str.extract(r"(\D+)(\d+)")
result["base"] = parts[0]
result["suffix"] = parts[1]
result = result.pivot(index=["Group", "suffix"], columns="base", values="Value").reset_index(drop=True)
result["Type"] = result["Number"].str.findall(r"[A-Z]+").str.join("")
result["Code"] = result["Number"].str.findall(r"\d+").str.join("")
result = result[["Group", "Type", "Code", "Value"]]

print(result.equals(test))

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