Excel BI - PowerQuery Challenge 188

excel-challenges
power-query
Store From Date To Date Amount Quarter A
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 188

Challenge Description

Store From Date To Date Amount Quarter A

Solutions

library(tidyverse)
library(readxl)

input = read_excel("Power Query/PQ_Challenge_188.xlsx", range = "A1:D4")
test  = read_excel("Power Query/PQ_Challenge_188.xlsx", range = "F1:H11")

result = input %>%
  mutate(date = map2(`From Date`, `To Date`, seq, by = "day"), 
         days = map_int(date, length),
         daily = Amount / days) %>%
  unnest(date) %>%
  mutate(quarter = quarter(date), 
         year = year(date) %>% as.character() %>% str_sub(3, 4),
         Quarter = paste0("Q",quarter,"-",year)) %>%
  summarise(Amount = sum(daily) %>% round(0), .by = c(Store, Quarter))

identical(result, test)
# [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

input = pd.read_excel("PQ_Challenge_188.xlsx", usecols="A:D", nrows=3)
test = pd.read_excel("PQ_Challenge_188.xlsx", usecols="F:H", nrows=11)
test.columns = test.columns.str.replace(".1", "")

result = input.assign(date=input.apply(lambda row: pd.date_range(row['From Date'], row['To Date'], freq='D'), axis=1)) \
    .explode('date') \
    .assign(days=lambda df: (df['To Date'] - df['From Date']).dt.days + 1) \
    .assign(daily=lambda df: df['Amount'] / df['days']) \
    .assign(quarter=lambda df: df['date'].dt.quarter) \
    .assign(year=lambda df: df['date'].dt.year.astype(str).str[2:4]) \
    .assign(Quarter=lambda df: 'Q' + df['quarter'].astype(str) + '-' + df['year']) \
    .groupby(['Store', 'Quarter', 'quarter', 'year']) \
    .agg(Amount=('daily', 'sum')) \
    .round(0) \
    .astype("int64") \
    .sort_values(by=['Store','year', 'quarter']) \
    .reset_index(drop=False) \
    .drop(columns=['quarter', 'year'])

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.