Excel BI - PowerQuery Challenge 156

excel-challenges
power-query
Name Subjects Teacher Biology Chemistry Geology
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 156

Challenge Description

Name Subjects Teacher Biology Chemistry Geology

Solutions

library(tidyverse)
library(readxl)

input1 = read_excel("Power Query/PQ_Challenge_156.xlsx", range = "A1:B10")
input2 = read_excel("Power Query/PQ_Challenge_156.xlsx", range = "D1:E5")

test = read_excel("Power Query/PQ_Challenge_156.xlsx", range = "G1:K6")

result = input1 %>% 
  left_join(input2, by = "Subjects") %>%
  pivot_wider(names_from = "Subjects", values_from = "Teacher", values_fill =  NA_character_) %>%
  select(Name, Biology, Chemistry, Geology, Physics)

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

input1 = pd.read_excel("PQ_Challenge_156.xlsx", usecols="A:B", nrows=10)
input2 = pd.read_excel("PQ_Challenge_156.xlsx", usecols="D:E", nrows=5)
test = pd.read_excel("PQ_Challenge_156.xlsx", usecols="G:K", nrows=6)

result = input1.merge(input2, on="Subjects", how="left")
result = result.pivot(index="Name", columns="Subjects", values="Teacher").reset_index()
result = result[["Name", "Biology", "Chemistry", "Geology", "Physics"]]

print(result.equals(test))

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