library(tidyverse)
library(readxl)
input = read_excel("Power Query/PQ_Challenge_161.xlsx", range = "A1:C30") %>% janitor::clean_names()
test = read_excel("Power Query/PQ_Challenge_161.xlsx", range = "E1:H30") %>% janitor::clean_names()
find_mode <- function(x) {
x <- na.omit(x)
if (length(x) == 0) return(NA)
freq <- table(x)
modes <- as.numeric(names(freq)[freq == max(freq)])
return(modes)
}
input2 = input %>%
group_by(group) %>%
mutate(group_min_week = min(week_no)) %>%
ungroup() %>%
mutate(week_start_date = as.Date(paste0(week_no, "1"), format = "%Y%U%u")) %>%
mutate(WM0 = week_start_date,
WM1 = week_start_date - weeks(1),
WM2 = week_start_date - weeks(2),
WM3 = week_start_date - weeks(3),
WM4 = week_start_date - weeks(4),
WM5 = week_start_date - weeks(5),
WM6 = week_start_date - weeks(6),
WM7 = week_start_date - weeks(7)) %>%
select(group, group_min_week, week_no, WM0, WM1, WM2, WM3, WM4, WM5, WM6, WM7) %>%
pivot_longer(cols = starts_with("WM"), names_to = "week_marker", values_to = "valid_week_start") %>%
mutate(group_min_week = as.Date(paste0(group_min_week, "1"), format = "%Y%U%u")) %>%
left_join(input %>%
mutate(week_start_date = as.Date(paste0(week_no, "1"), format = "%Y%U%u")) ,
by = c("group", "valid_week_start" = "week_start_date")) %>%
filter(valid_week_start >= group_min_week) %>%
group_by(group, week_no.x) %>%
mutate(no_groups = n()) %>%
ungroup() %>%
group_by(group, week_no.x, no_groups) %>%
summarise(winning_nos = list(winning_no), .groups = 'drop') %>%
ungroup() %>%
arrange(group, desc(week_no.x)) %>%
mutate(winning_nos_ = winning_nos) %>%
mutate(winning_nos = map(winning_nos, ~na.omit(.x)))
input3 = input2 %>%
mutate(mode = map(winning_nos, find_mode)) %>%
mutate(mode = map_chr(mode, ~paste(., collapse = ", "))) %>%
mutate(mode = if_else(no_groups < 8, NA, mode))
input4 = input %>%
left_join(input3, by = c("group", "week_no" = "week_no.x")) %>%
left_join(test, by = c("group", "week_no" = "week_no")) %>%
select(1,2,7,9) %>%
mutate(check = mode == max_occurred_no)Excel BI - PowerQuery Challenge 161
excel-challenges
power-query
Group For each group, find the maximum occurring Winning numbers within a period of 7 weeks . So if first period is 202310 (2023 is year and 10 is week number), then range will be 202310-202303. Also note, weeknumbers are discontinuous in Group B and C. So if 202418 is starting week, then range will be 202418-202411 even though this range may have missing week numbers. Also note the discontinuity in Group A because of changing year.
Challenge Description
Group For each group, find the maximum occurring Winning numbers within a period of 7 weeks . So if first period is 202310 (2023 is year and 10 is week number), then range will be 202310-202303. Also note, weeknumbers are discontinuous in Group B and C. So if 202418 is starting week, then range will be 202418-202411 even though this range may have missing week numbers. Also note the discontinuity in Group A because of changing year.
Solutions
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_data = pd.read_excel("PQ_Challenge_161.xlsx", usecols="A:C", nrows=30)
input_data.columns = [c.strip().lower() for c in input_data.columns]
test = pd.read_excel("PQ_Challenge_161.xlsx", usecols="E:H", nrows=30)
test.columns = [c.strip().lower() for c in test.columns]
def find_mode(values):
values = pd.Series(values).dropna()
if values.empty:
return None
counts = values.value_counts()
top = counts.max()
return ", ".join(str(v) for v in sorted(counts[counts == top].index))
df = input_data.copy()
df["group_min_week"] = df.groupby("group")["week_no"].transform("min")
df["week_start_date"] = pd.to_datetime(df["week_no"].astype(str) + "1", format="%Y%U%w")
for i in range(8):
df[f"WM{i}"] = df["week_start_date"] - pd.to_timedelta(7 * i, unit="D")
long = df.melt(id_vars=["group", "group_min_week", "week_no"], value_vars=[f"WM{i}" for i in range(8)], var_name="week_marker", value_name="valid_week_start")
long["group_min_week"] = pd.to_datetime(long["group_min_week"].astype(str) + "1", format="%Y%U%w")
inp2 = input_data.copy()
inp2["week_start_date"] = pd.to_datetime(inp2["week_no"].astype(str) + "1", format="%Y%U%w")
joined = long.merge(inp2, left_on=["group", "valid_week_start"], right_on=["group", "week_start_date"], how="left")
joined = joined[joined["valid_week_start"] >= joined["group_min_week"]]
joined["no_groups"] = joined.groupby(["group", "week_no_x"])["winning_no"].transform("size")
agg = joined.groupby(["group", "week_no_x", "no_groups"], as_index=False).agg(winning_nos=("winning_no", list))
agg["mode"] = agg["winning_nos"].map(find_mode)
agg["mode"] = agg["mode"].where(agg["no_groups"] >= 8)
result = input_data.merge(agg[["group", "week_no_x", "mode"]], left_on=["group", "week_no"], right_on=["group", "week_no_x"], how="left").drop(columns="week_no_x")
check = result.merge(test, on=["group", "week_no"], how="left")
check["check"] = check["mode"] == check["max_occurred_no"]
print(check[["group", "week_no", "mode", "max_occurred_no", "check"]])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
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.