Excel BI - PowerQuery Challenge 198

excel-challenges
power-query
Date Value Running Total RESULT PROBLEM Find the running total on the basis of max value for the month.
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 198

Challenge Description

Date Value Running Total RESULT PROBLEM Find the running total on the basis of max value for the month.

Solutions

library(readxl)
library(tidyverse)

path  = "Power Query/PQ_Challenge_198.xlsx"

input = read_excel(path, range = "A1:B20")
test  = read_excel(path, range = "D1:F20")

result = input %>%
  mutate(month = month(Date)) %>%
  summarise(Max = max(Value), .by = month) %>%
  mutate(`Running Total` = cumsum(Max)) %>%
  right_join(input %>% mutate(month = month(Date)), by = "month") %>%
  select(Date, Value, `Running Total`)

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

path = "PQ_Challenge_198.xlsx"

input = pd.read_excel(path, usecols="A:B")
test  = pd.read_excel(path, usecols="D:F")
test.columns = test.columns.str.replace('.1', '')

result = input.assign(month=input['Date'].dt.month) \
              .groupby('month') \
              .agg(Max=('Value', 'max')) \
              .assign(Running_Total=lambda x: x['Max'].cumsum()) \
              .merge(input.assign(month=input['Date'].dt.month), on='month', how='right') \
              .rename(columns={'Running_Total': 'Running Total'}) \
              .loc[:, ['Date', 'Value', 'Running Total']]

print(result.equals(test)) # 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 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 easy to moderate:

  • The transformation rule is readable, but the final layout still requires a careful implementation.