Excel BI - PowerQuery Challenge 155

excel-challenges
power-query
Extract the valid times from the given strings. Time appears as HH:MM
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 155

Challenge Description

Extract the valid times from the given strings. Time appears as HH:MM

Solutions

library(tidyverse)
library(readxl)

input = read_excel("Power Query/PQ_Challenge_155.xlsx", range = "A1:A10")
test  = read_excel("Power Query/PQ_Challenge_155.xlsx", range = "D1:D10")

extract = function(string) {
  subs = string %>% 
    str_extract_all("\\d{1,2}\\:\\d{2}") %>%
    unlist()

  subs = purrr::map_chr(subs, function(x) {
    ifelse(
      as.numeric(strsplit(x, ":")[[1]][1]) %in% 0:23 & as.numeric(strsplit(x, ":")[[1]][2]) %in% 0:59,
      x,
      NA_character_
    )
  }) %>%
    na.omit() %>%
    str_c(collapse = ", ")

  return(subs)
}

result = input %>% 
  mutate(extracted = map_chr(String, extract),
         extracted = ifelse(extracted == "", NA_character_, extracted))

identical(result$extracted, test$`Expected Answer`)
# [1] TRUE

  • Logic:

    • Reads the workbook range needed for the challenge

    • Builds helper columns that drive the final output

    • Uses direct pattern parsing where the workbook encodes logic in text

  • 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 re
import pandas as pd

input_data = pd.read_excel("PQ_Challenge_155.xlsx", usecols="A", nrows=10)
test = pd.read_excel("PQ_Challenge_155.xlsx", usecols="D", nrows=10)

def extract_valid_times(text):
    matches = re.findall(r"\d{1,2}:\d{2}", str(text))
    valid = []
    for item in matches:
        hh, mm = item.split(":")
        if int(hh) in range(24) and int(mm) in range(60):
            valid.append(item)
    return ", ".join(valid) if valid else None

result = input_data.assign(extracted=input_data["String"].map(extract_valid_times))
print(result["extracted"].equals(test["Expected Answer"]))

  • Logic:

    • Reads the workbook range needed for the challenge

    • Builds helper columns that drive the final output

    • Uses direct pattern parsing where the workbook encodes logic in text

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