Excel BI - PowerQuery Challenge 352

excel-challenges
power-query
Date User Field Old Value New Value Old Description
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 352

Challenge Description

Date User Field Old Value New Value Old Description

Solutions

library(tidyverse)
library(readxl)

path <- "Power Query/300-399/352/PQ_Challenge_352.xlsx"
input <- read_excel(path, range = "A1:E49")
test <- read_excel(path, range = "G1:N21")

result = input %>%
  fill(Date, User, .direction = "down") %>%
  rename(Old = `Old Value`, New = `New Value`) %>%
  pivot_wider(
    names_from = Field,
    values_from = c(Old, New),
    names_glue = "{.value} {Field}",
    names_vary = "slowest"
  )

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

  • 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 = "Power Query/300-399/352/PQ_Challenge_352.xlsx"

input = pd.read_excel(path, usecols="A:E", nrows=49).rename(columns={"Old Value": "Old", "New Value": "New"})
input[['Date', 'User']] = input[['Date', 'User']].ffill()

test = pd.read_excel(path, usecols="G:N", nrows=20).rename(columns=lambda col: col.replace(".1", ""))

result = input.pivot_table(
    index=[col for col in input.columns if col not in ['Field', 'Old', 'New']],
    columns="Field", values=["Old", "New"], aggfunc='first'
)

result.columns = [f"{val[0]} {val[1]}" for val in result.columns]
result = result.reset_index()[['Date', 'User', 'Old Description', 'New Description', 'Old Signal', 'New Signal', 'Old Product Line', 'New Product Line']]

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

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