Excel BI - PowerQuery Challenge 365

excel-challenges
power-query
Between START and a successful END, assign process ID and extract users, types and values as shown. Don’t extract if End is not successful. Process ID will sequentially increase for next START and successful END.
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 365

Challenge Description

Between START and a successful END, assign process ID and extract users, types and values as shown. Don’t extract if End is not successful. Process ID will sequentially increase for next START and successful END.

Solutions

library(tidyverse)
library(readxl)

path <- "Power Query/300-399/365/PQ_Challenge_365.xlsx"
input <- read_excel(path, range = "A1:A19")
test <- read_excel(path, range = "C1:F6")

x <- input %>%
  mutate(
    event = str_extract(Data, "^(START|DATA|END)"),
    proc = cumsum(event == "START" & lag(event, default = "END") == "END")
  ) %>%
  mutate(ok = any(Data == "END:Success"), .by = proc) %>%
  filter(ok) %>%
  mutate(ProcessID = dense_rank(proc))

starts <- x %>%
  filter(event == "START") %>%
  transmute(
    proc,
    ProcessID,
    idx = row_number(),
    .by = proc,
    User = str_match(Data, "User_([^:]+)")[, 2],
    Type = str_match(Data, "Type_([^:]+)")[, 2]
  )

datas <- x %>%
  filter(event == "DATA") %>%
  transmute(
    proc,
    idx = row_number(),
    .by = proc,
    Value = as.numeric(str_match(Data, "Value_(\\d+)")[, 2])
  )

result <- starts %>%
  left_join(datas, by = c("proc", "idx")) %>%
  select(ProcessID, User, Type, Value)

all.equal(result, test)
#> [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 pandas as pd
import re

path = "Power Query/300-399/365/PQ_Challenge_365.xlsx"
input_df = pd.read_excel(path, usecols="A", nrows=19)
test = pd.read_excel(path, usecols="C:F", nrows=5)

df = input_df.copy()
df['event'] = df['Data'].str.extract(r'^(START|DATA|END)')
df['proc'] = (df['event'].eq('START') & df['event'].shift(fill_value='END').eq('END')).cumsum()
df['ok'] = df.groupby('proc')['Data'].transform(lambda x: x.eq('END:Success').any())
df = df.loc[df['ok']].copy()
df['ProcessID'] = df['proc'].rank(method='dense').astype("int64")

starts = df.loc[df['event'] == 'START', ['proc', 'ProcessID', 'Data']].copy()
starts['idx'] = starts.groupby('proc').cumcount() + 1
starts['User'] = starts['Data'].str.extract(r'User_([^:]+)')[0]
starts['Type'] = starts['Data'].str.extract(r'Type_([^:]+)')[0]

datas = df.loc[df['event'] == 'DATA', ['proc', 'Data']].copy()
datas['idx'] = datas.groupby('proc').cumcount() + 1
datas['Value'] = datas['Data'].str.extract(r'Value_(\d+)')[0].astype("int64")

result = (
    starts.merge(datas[['proc', 'idx', 'Value']], on=['proc', 'idx'], how='left')
    [['ProcessID', 'User', 'Type', 'Value']]
)

print(result.equals(test))

  • Logic:

    • Reads the workbook range needed for the challenge

    • Aggregates or ranks values at the relevant grouping level

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

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