Excel BI - PowerQuery Challenge 182

excel-challenges
power-query
Date Name Data Value 5/1/2014 Data1
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 182

Challenge Description

Date Name Data Value 5/1/2014 Data1

Solutions

library(tidyverse)
library(openxlsx2)

path = "Power Query/PQ_Challenge_182.xlsx"


input = wb_read(path, rows = 1:20, cols = "A:D")
test  = wb_read(path, rows = 1:11, cols = "F:I", col_names = FALSE, detect_dates = TRUE) %>%
  mutate(`F` = str_replace(`F`, "5/1/2014", "2014-05-01"))

result = input %>%
  pivot_wider( names_from = "Data", values_from = "Value") %>%
  mutate(rn = row_number())

r1 = result %>%
  summarise(Name = format(Date), Data1 = "Data1", Data2 = "Data2", Data3 = "Data3", rn = 0, .by = Date) %>%
  distinct()

r2 = result %>%
  rbind(r1) %>%
  arrange(Date, rn) %>%
  select(-c(Date, rn))

colnames(r2) = colnames(test)

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

    • 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

input_data = pd.read_excel("PQ_Challenge_182.xlsx", usecols="A:D", nrows=20)
test = pd.read_excel("PQ_Challenge_182.xlsx", usecols="F:I", nrows=11, header=None)
test.iloc[:, 0] = test.iloc[:, 0].astype(str).str.replace("5/1/2014", "2014-05-01", regex=False)

result = input_data.pivot(index="Date", columns="Data", values="Value").reset_index()
result["rn"] = range(1, len(result) + 1)
r1 = result.groupby("Date", as_index=False).first()[["Date"]].copy()
r1["Data1"] = "Data1"
r1["Data2"] = "Data2"
r1["Data3"] = "Data3"
r1["rn"] = 0
r2 = pd.concat([result, r1], ignore_index=True).sort_values(["Date", "rn"]).drop(columns=["Date", "rn"])
r2.columns = test.columns

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