Excel BI - PowerQuery Challenge 186

excel-challenges
power-query
Merge both the tables. Delivery date will be populated against calendar date for +1 and -1 dates also apart from matched rows. If there is a tie when populating for +1 and -1 dates, then immediate matched row will take precedence. This case is illustrated by 25-May-24 and 26-May-24.
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 186

Challenge Description

Merge both the tables. Delivery date will be populated against calendar date for +1 and -1 dates also apart from matched rows. If there is a tie when populating for +1 and -1 dates, then immediate matched row will take precedence. This case is illustrated by 25-May-24 and 26-May-24.

Solutions

library(tidyverse)
library(readxl)
library(janitor)

input1 = read_excel("Power Query/PQ_Challenge_186.xlsx", range = "A1:A30") %>% clean_names()
input2 = read_excel("Power Query/PQ_Challenge_186.xlsx", range = "C1:D7") %>% clean_names()
test   = read_excel("Power Query/PQ_Challenge_186.xlsx", range = "F1:H30") %>% clean_names()

marked_dates <- input2 %>%
  mutate(
    preceding_date = delivery_date - days(1),
    following_date = delivery_date + days(1)
  ) %>%
  pivot_longer(
    cols = c(preceding_date, delivery_date, following_date),
    names_to = "type",
    values_to = "marked_date"
  ) %>%
  mutate(type = factor(type, levels = c("preceding_date", "following_date","delivery_date"), 
                       ordered = TRUE))

calendar_with_markings <- input1 %>%
  left_join(marked_dates, by = c("calendar_date" = "marked_date")) %>%
  mutate(marked = !is.na(vendor)) %>%
  group_by(calendar_date) %>%
  mutate(proper_type = max(type, na.rm = TRUE)) %>%
  ungroup() %>%
  filter(proper_type == type | is.na(proper_type)) %>%
  mutate(delivery_date = case_when(
    type == "delivery_date" ~ calendar_date,
    type == "preceding_date" ~ calendar_date + days(1),
    type == "following_date" ~ calendar_date - days(1)
  )) %>%
  select(calendar_date, delivery_date, vendor)

identical(test, calendar_with_markings)
# [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
import numpy as np

input1 = pd.read_excel("PQ_Challenge_186.xlsx", usecols="A")
input2 = pd.read_excel("PQ_Challenge_186.xlsx",  usecols="C:D", nrows=6)
test = pd.read_excel("PQ_Challenge_186.xlsx",  usecols="F:H", 
                     names=["Calendar Date", "Delivery Date", "Vendor"])
input1["Calendar Date"] = pd.to_datetime(input1["Calendar Date"])
input2["Delivery Date"] = pd.to_datetime(input2["Delivery Date"])
input2['preceding_date'] = input2['Delivery Date'] - pd.Timedelta(days=1)
input2['following_date'] = input2['Delivery Date'] + pd.Timedelta(days=1)
input2['date'] = input2['Delivery Date']
marked_dates = input2.melt(id_vars=['Vendor', 'date'], 
                           value_vars=['preceding_date', 'Delivery Date', 'following_date'],
                           var_name='type', value_name='marked_date').\
                            sort_values('marked_date').\
                                reset_index(drop=True)
marked_dates['type'] = pd.Categorical(marked_dates['type'], 
                                      categories=['preceding_date', 'following_date', 'Delivery Date'], 
                                      ordered=True)
calendar_with_marks = input1.merge(marked_dates, 
                                      left_on='Calendar Date',
                                      right_on='marked_date', 
                                      how='left')
calendar_with_marks['marked'] = ~calendar_with_marks['Vendor'].isna()
calendar_with_marks['important'] = calendar_with_marks.\
    groupby('Calendar Date')['type'].transform(lambda x: x == x.max())
calendar_with_marks = calendar_with_marks[calendar_with_marks['type'].isna() |\
                                           calendar_with_marks['important']].reset_index(drop=True)
calendar_with_marks = calendar_with_marks[["Calendar Date", "date", "Vendor"]]
calendar_with_marks.columns = test.columns

print(calendar_with_marks.equals(test)) # 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

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