Excel BI - PowerQuery Challenge 178

excel-challenges
power-query
Emp Old Role New Role Old Org New Org Change
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 178

Challenge Description

Emp Old Role New Role Old Org New Org Change

Solutions

library(tidyverse)
library(readxl)

input = read_excel("Power Query/PQ_Challenge_178.xlsx", range = "A1:E5")
test  = read_excel("Power Query/PQ_Challenge_178.xlsx", range = "H1:K5")

result = input %>%
  pivot_longer(-Emp, names_to = "Change", values_to = "Value") %>%
  separate(Change, into = c("Type", "Change"), sep = " ") %>%
  pivot_wider(names_from = Type, values_from = Value) %>%
  drop_na() 

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

  • 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 = pd.read_excel("PQ_Challenge_178.xlsx", usecols = "A:E", nrows=5)
test = pd.read_excel("PQ_Challenge_178.xlsx", usecols="H:K", nrows=5, names=["Emp", "Change", "Old", "New"])
test = test.sort_values(by = ["Emp", "Change"]).reset_index(drop=True)

result = input.melt(id_vars="Emp", var_name="Change", value_name="Value")
result[["Type", "Change"]] = result["Change"].str.split(" ", expand=True) 
result = result.pivot(index=["Emp", "Change"], columns="Type", values="Value").dropna().reset_index()
result = result[["Emp", "Change", "Old", "New"]] 
result.columns.name = None
result = result.sort_values(by=["Emp", "Change"]).reset_index(drop=True)

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

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