Excel BI - PowerQuery Challenge 229

excel-challenges
power-query
Divide the amount across month-years in a pro-rata manner on the basis of number of days. Due to rounding, sum total may differ by +-1, so we can ignore that.
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 229

Challenge Description

Divide the amount across month-years in a pro-rata manner on the basis of number of days. Due to rounding, sum total may differ by +-1, so we can ignore that.

Solutions

library(tidyverse)
library(readxl)

path = "Power Query/PQ_Challenge_229.xlsx"
input = read_excel(path, range = "A1:D6")
test  = read_excel(path, range = "F1:H16")

result = input %>%
  mutate(days = as.numeric(as.Date(`To Date`) - as.Date(`From Date`)) + 1, 
         daily = Amount / days) %>%
  rowwise() %>%
  mutate(date = list(seq(`From Date`, `To Date`, by = "day"))) %>%
  unnest(date) %>%
  mutate(`Month - Year` = paste0(str_pad(month(date), width = 2, "0", side = "left"), "-", str_sub(year(date), 3, 4))) %>%
  summarise(Amount = round(sum(daily),0), .by = c(Transaction, `Month - Year`))

all.equal(result, test, check.attributes = FALSE)
# [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 pandas as pd

path = "PQ_Challenge_229.xlsx"
input = pd.read_excel(path, usecols="A:D", nrows=5)
test = pd.read_excel(path, usecols="F:H", nrows=16).rename(columns=lambda x: x.replace('.1', ''))
test = test.sort_values(by=['Transaction', 'Month-Year']).reset_index(drop=True)

input['From Date'] = pd.to_datetime(input['From Date'])
input['To Date'] = pd.to_datetime(input['To Date'])
input['days'] = (input['To Date'] - input['From Date']).dt.days + 1
input['daily'] = input['Amount'] / input['days']

expanded = input.assign(date_range=input.apply(lambda row: pd.date_range(row['From Date'], row['To Date']), axis=1)).explode('date_range')
expanded['Month-Year'] = expanded['date_range'].dt.strftime('%m-%y')

result = expanded.groupby(['Transaction', 'Month-Year']).agg({'daily': 'sum'}).reset_index()
result['Amount'] = result['daily'].round(0).astype("int64")
result = result.drop(columns=['daily']).sort_values(by=['Transaction', 'Month-Year']).reset_index(drop=True)

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