Excel BI - PowerQuery Challenge 167

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

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 167

Challenge Description

Transpose the given problem table into result table.

Solutions

library(tidyverse)
library(readxl)

input = read_excel("Power Query/PQ_Challenge_167.xlsx", range = "A1:D14")
test  = read_excel("Power Query/PQ_Challenge_167.xlsx", range = "G1:K16")

result = input %>%
  mutate(`Camp No` = parse_number(`Camp No`),
         group = cumsum(!is.na(`Camp No`)),
         group_name = ifelse(`Camp No` == group, Vaccine, NA_character_)) %>%
  fill(group_name) %>%
  filter(is.na(`Camp No`)) %>%
  select(Vaccine = group_name,
         Name = Vaccine, 
         `Notification Date` = `Notification Date`,
         `Administration Date` = `Administration Date`) %>%
  pivot_wider(names_from = Name, values_from = c(`Notification Date`, `Administration Date`)) %>%
  pivot_longer(cols = -Vaccine, names_to = "Name", values_to = "Date") %>%
  separate(Name, into = c("Event", "Name"), sep = "_", remove = TRUE) %>%
  pivot_wider(names_from = Event, values_from = Date) %>%
  arrange(desc(Vaccine), Name) %>%
  mutate(`Camp No` = row_number() %>% as.numeric(), .by = Vaccine) %>%
  relocate(`Camp No`, .before = Vaccine) %>%
  mutate(Notified = ifelse(is.na(`Notification Date`), "No", "Yes"),
         Administered = ifelse(is.na(`Administration Date`), ifelse(is.na(`Notification Date`),"NA", "No"), "Yes")) %>%
  select(-c(4,5))

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

  • 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
import numpy as np

input = pd.read_excel('PQ_Challenge_167.xlsx', usecols="A:D", nrows=14)
test = pd.read_excel('PQ_Challenge_167.xlsx', usecols="G:K", nrows=16)
test.rename(columns={'Vaccine.1': 'Vaccine', 'Camp No.1':'Camp No'}, inplace=True)

input['group_name'] = np.where(input['Camp No'].notna().cumsum() == input['Camp No'], input['Vaccine'], np.nan)
input['group_name'] = input.groupby('group_name')['group_name'].ffill()
input = input[input['Camp No'].isna()]
input = input.pivot_table(index='group_name', columns='Vaccine', values=['Notification Date', 'Administration Date'], aggfunc='first').reset_index()
input = input.melt(id_vars=['group_name'], var_name=['Event', 'Name'], value_name='Date')
input = input.pivot_table(index=['group_name', 'Name'], columns='Event', values='Date', aggfunc='first', dropna=False).reset_index()
input = input.sort_values(by=['group_name', 'Name'], ascending=[False, True])
input['Notified'] = np.where(input['Notification Date'].notna(), 'Yes', 'No')
input['Administered'] = np.where(input['Administration Date'].notna(), 'Yes', np.where(input['Notification Date'].notna(), 'No', np.nan))
input['Camp No'] = input.groupby('group_name').cumcount() + 1
input = input[['Camp No','group_name', 'Name', 'Notified', 'Administered']].reset_index(drop=True)
input.rename(columns={'group_name': 'Vaccine'}, inplace=True)

input = input.astype(str)
test = test.astype(str)

print(input.equals(test))

# Output: 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

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