Excel BI - PowerQuery Challenge 375

excel-challenges
power-query
Step User Action Value Final Typed Aditi
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 375

Challenge Description

Step User Action Value Final Typed Aditi

Solutions

library(dplyr)
library(readxl)
library(purrr)

path <- "300-399/375/PQ_Challenge_375.xlsx"
input <- read_excel(path, range = "A1:D43")
test <- read_excel(path, range = "G1:H3") |>
  filter(!is.na(User))


simulate_typing <- function(df) {
  history <- list()
  current <- ""

  walk2(df$Action, df$Value, \(action, value) {
    if (action == "Type") {
      history[[length(history) + 1]] <<- current
      current <<- paste0(current, value)
    } else if (action == "Backspace") {
      n <- as.integer(value)
      history[[length(history) + 1]] <<- current
      current <<- substr(current, 1, nchar(current) - n)
    } else if (action == "Undo") {
      if (length(history) > 0) {
        current <<- history[[length(history)]]
        history[[length(history)]] <<- NULL
      }
    }
  })

  tibble(`Final Typed` = current)
}

result <- input |>
  arrange(Step) |>
  group_by(User) |>
  group_modify(~ simulate_typing(.x)) |>
  ungroup()

identical(result, test)
# TRUE

  • Logic:

    • Reads the workbook range needed for the challenge

    • Aggregates or ranks values at the relevant grouping level

  • 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
from collections import defaultdict

path = "300-399/375/PQ_Challenge_375.xlsx"
input_df = pd.read_excel(path, usecols="A:D", nrows=43)
test = (
    pd.read_excel(path, usecols="G:H", nrows=2)
    .rename(columns={"User.1": "User"})
)


def simulate_typing(df):
    history = defaultdict(list) 
    current = defaultdict(str) 
    for _, row in df.iterrows():
        user = row["User"]
        action = row["Action"]
        value = row["Value"]

        if action == "Type":
            history[user].append(current[user])
            current[user] += str(value)
        elif action == "Backspace":
            history[user].append(current[user])
            n = int(value)
            current[user] = current[user][:-n]
        elif action == "Undo":
            if history[user]:
                current[user] = history[user].pop()

    return dict(current)


results = simulate_typing(input_df)
result = (
    pd.DataFrame(list(results.items()), columns=["User", "Final Typed"])
    .sort_values("User")
    .reset_index(drop=True)
)

print(result.equals(test))
# True

  • Logic:

    • Reads the workbook range needed for the challenge

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