Excel BI - PowerQuery Challenge 150

excel-challenges
power-query
Items Time In Time Out Duration A B
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 150

Challenge Description

Items Time In Time Out Duration A B

Solutions

library(tidyverse)
library(readxl)
library(lubridate)
library(hms)

input = read_excel("Power Query/PQ_Challenge_150.xlsx", range = "A1:D11") %>%
  janitor::clean_names() 
test  = read_excel("Power Query/PQ_Challenge_150.xlsx", range = "F1:I11") %>%
  janitor::clean_names() %>% 
  mutate(across(c(time_in, time_out), ~as_hms(.x)))

result = input %>%
  mutate(across(c(time_in, time_out), ~as_hms(.x))) %>%
  group_by(empty = is.na(time_in)) %>%
  mutate(nr = row_number()) %>%
  ungroup() %>%
  group_by(nr) %>%
  mutate(time_in = if_else(empty, first(time_out), time_in),
         time_out = if_else(empty, time_in + dminutes(round(duration * 60,0)), time_out)) %>%
  ungroup() %>%
  select(-c(empty, nr))

identical(result, test)
#> [1] TRUE

  • Logic:

    • Reads the workbook range needed for the challenge

    • 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_150.xlsx", usecols="A:D", nrows=11)
input_data.columns = [c.strip().lower() for c in input_data.columns]
test = pd.read_excel("PQ_Challenge_150.xlsx", usecols="F:I", nrows=11)
test.columns = [c.strip().lower() for c in test.columns]

result = input_data.copy()
result["empty"] = result["time_in"].isna()
result["nr"] = result.groupby("empty").cumcount() + 1

def fill_pair(group):
    group = group.copy()
    if group["empty"].iloc[0]:
        group["time_in"] = group["time_out"].iloc[0]
        group["time_out"] = group["time_in"] + pd.to_timedelta(round(group["duration"].iloc[0] * 60), unit="m")
    return group

result = result.groupby("nr", group_keys=False).apply(fill_pair).drop(columns=["empty", "nr"]).reset_index(drop=True)
print(result.equals(test))

  • Logic:

    • Reads the workbook range needed for the challenge

    • Aggregates or ranks values at the relevant grouping level

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