Excel BI - PowerQuery Challenge 208

excel-challenges
power-query
for each month group for which 3 years moving averages (MV) are given.
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 208

Challenge Description

for each month group for which 3 years moving averages (MV) are given.

Solutions

library(tidyverse)
library(readxl)

path = "Power Query/PQ_Challenge_208.xlsx"
input = read_xlsx(path, range = "A1:C35")

find_defects <- function(input) {
  generate_integer_set_with_mean <- function(target_mean) {
    x1 <- sample(1:(2 * target_mean), 1)
    x2 <- sample(1:(2 * target_mean), 1)
    x3 <- 3 * target_mean - x1 - x2
    
    while (x3 <= 0 || x3 > 2 * target_mean) {
      x1 <- sample(1:(2 * target_mean), 1)
      x2 <- sample(1:(2 * target_mean), 1)
      x3 <- 3 * target_mean - x1 - x2
    }
    return(c(x1, x2, x3))
  }
  initial_set = generate_integer_set_with_mean(input$`3 Year MV`[which(!is.na(input$`3 Year MV`))[1]])
  res = input %>%
    mutate(defects = NA) %>%
    slice(1:3) %>%
    mutate(defects = initial_set) %>%
    bind_rows(input %>% slice(4:n()))
  for (i in 4:nrow(res) - 1) {
    res$defects[i] = 3 * res$`3 Year MV`[i + 1] - res$defects[i - 2] - res$defects[i - 1]
  }
  return(res)
}

result = input %>%
  split(.$Month) %>%
  map(find_defects) %>%
  bind_rows()

print(result)

  • Logic:

    • Builds helper columns that drive the final output

    • Applies the rule iteratively until the output is complete

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

path = "PQ_Challenge_208.xlsx"
input = pd.read_excel(path, usecols="A:C")

def find_defects(input):
    def generate_integer_set_with_mean(target_mean):
        x1 = random.randint(1, 2 * target_mean)
        x2 = random.randint(1, 2 * target_mean)
        x3 = 3 * target_mean - x1 - x2
        
        while x3 <= 0 or x3 > 2 * target_mean:
            x1 = random.randint(1, 2 * target_mean)
            x2 = random.randint(1, 2 * target_mean)
            x3 = 3 * target_mean - x1 - x2
        
        return [x1, x2, x3]
    
    initial_set = generate_integer_set_with_mean(input['3 Year MV'].dropna().iloc[0])
    
    input['Defects'] = [initial_set[0], initial_set[1], initial_set[2]] + [None] * (len(input) - 3)

    for i in range(3, len(input) - 1):
        input['Defects'].iloc[i] = 3 * input['3 Year MV'].iloc[i+1] - sum(input['Defects'].iloc[i-3:i])

    return input

output = input.groupby('Month').apply(find_defects).reset_index(drop=True)

print(output)

  • Logic:

    • Reads the workbook range needed for the challenge

    • Aggregates or ranks values at the relevant grouping level

    • 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 easy to moderate:

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