Excel BI - PowerQuery Challenge 162

excel-challenges
power-query
Extract alphbet and two digit numbers strings if alphabet and two digit numbers are separated by a special character.
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 162

Challenge Description

Extract alphbet and two digit numbers strings if alphabet and two digit numbers are separated by a special character.

Solutions

library(tidyverse)
library(readxl)

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

result = input %>%
  mutate(result = str_extract_all(String, "([[:alpha:]])[^[:alnum:]]([[:digit:]]{2})")) %>%
  unnest_longer(result, keep_empty = TRUE) %>%
  mutate(result = str_remove(result, "[^[:alnum:]]")) %>%
  group_by(String) %>%
  summarise(Result = paste(result, collapse = ", ")) %>%
  ungroup() %>%
  mutate(Result = if_else(Result == "NA", NA, Result))
  

test1 = test %>%
  left_join(result, by = c("String" = "String"), suffix = c(".test", ".result"))

all.equal(test1$Result.test, test1$Result.result)
# [1] 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

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

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

pattern = re.compile(r"([A-Za-z])[^A-Za-z0-9](\d{2})")

def extract_pairs(text):
    matches = pattern.findall(str(text))
    if not matches:
        return None
    return ", ".join(a + b for a, b in matches)

result = input_data.assign(Result=input_data["String"].map(extract_pairs))
check = test.merge(result, on="String", how="left", suffixes=(".test", ".result"))
print(check["Result.test"].equals(check["Result.result"]))

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