Excel BI - PowerQuery Challenge 201

Table1 Buyer / Items Clothes Eggs Fish Fruits

Illustration for Excel BI - PowerQuery Challenge 201

Challenge Description

Table1 Buyer / Items Clothes Eggs Fish Fruits

Solutions

R Solution

library(tidyverse)
library(readxl)

path = "Power Query/PQ_Challenge_201.xlsx"
input1 = read_excel(path, range = "A2:C7")
input2 = read_excel(path, range = "A10:C16")
test = read_excel(path, range = "E1:K6")

i1 = input1 %>%
  mutate(date = map2(`Buy Date From`, `Buy Date To`, seq, by = "day")) %>%
  unnest(date) %>%
  select(Buyer, date)

i2 = input2 %>%
  mutate(`Stock Start Date` = replace_na(`Stock Start Date`, min(`Stock Start Date`, na.rm = TRUE)),
         `Stock Finish Date` = replace_na(`Stock Finish Date`, max(i1$date, na.rm = TRUE))) %>%
  mutate(date = map2(`Stock Start Date`, `Stock Finish Date`, seq, by = "day"))  %>%
  unnest(date) %>%
  select(Items, date)

result = i1 %>%
  inner_join(i2, by = c("date")) %>%
  pivot_wider(names_from = Items, values_from = date, values_fn = length) %>%
  select(`Buyer / Items` = 1, sort(colnames(.), decreasing = FALSE)) %>%
  mutate(across(-c(1), ~ifelse(is.na(.), ., "X")))

all.equal(result, test)
# [1] TRUE

R Analysis

• Logic:

  • Reads the workbook range needed for the challenge

  • Reshapes the data into the structure required by the result table

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

Python Solution

import pandas as pd

path = "PQ_Challenge_201.xlsx"
input1 = pd.read_excel(path, usecols="A:C", skiprows=1, nrows = 5)
input2 = pd.read_excel(path, usecols="A:C", skiprows=9, nrows = 6)
test = pd.read_excel(path, usecols="E:K", nrows = 5)

i1 = input1.assign(date=input1.apply(lambda row: pd.date_range(row['Buy Date From'], row['Buy Date To'], freq='D'), axis=1)) \
          .explode('date') \
          .filter(['Buyer', 'date'])

i2 = input2.assign(**{
    'Stock Start Date': input2['Stock Start Date'].fillna(input2['Stock Start Date'].min()),
    'Stock Finish Date': input2['Stock Finish Date'].fillna(i1['date'].max())
    })
i2['date'] = i2.apply(lambda row: pd.date_range(row['Stock Start Date'], row['Stock Finish Date'], freq='D'), axis=1)
i2 = i2.explode('date').filter(['Items', 'date'])

result = pd.merge(i1, i2, on='date') \
            .assign(X='X') \
            .pivot_table(index='Buyer', columns='Items', values='X', aggfunc='first') \
            .reset_index() \
            .rename(columns={'Buyer': 'Buyer / Items'}) \
            .rename_axis(None, axis=1)
            
print(result.equals(test))  # True

Python

• Logic:

  • Reads the workbook range needed for the challenge

  • Reshapes the data into the structure required by the result table

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

Related Challenges

Explore more challenges from the same series:

• Excel BI - PowerQuery Challenge 200