Excel BI - PowerQuery Challenge 168

excel-challenges
power-query
Store Item Store 1 J A G
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 168

Challenge Description

Store Item Store 1 J A G

Solutions

library(tidyverse)
library(readxl)

input = read_excel("Power Query/PQ_Challenge_168.xlsx", range = "A1:B10")
test  = read_excel("Power Query/PQ_Challenge_168.xlsx", range = "D1:E10")

result = input %>%
  mutate(nr = row_number(), 
         chars = as.vector(Item) %>% sort() %>% list(), 
         .by = Store) %>%
  mutate(Item = map2_chr(chars, nr,
                     ~str_c(.x, collapse = "/") 
                     %>% str_sub(1, ifelse(.y > 1, .y*2-1, 1)))) %>%
  select(Store, Item) 

identical(result, test)
# [1] TRUE

  • Logic:

    • Reads the workbook range needed for the challenge

    • Builds helper columns that drive the final output

    • Uses direct pattern parsing where the workbook encodes logic in text

  • 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

# Read input data
input_data = pd.read_excel('PQ_Challenge_168.xlsx', sheet_name='Sheet1', usecols='A:B', nrows=10)
test_data = pd.read_excel('PQ_Challenge_168.xlsx', sheet_name='Sheet1', usecols='D:E', nrows=10)

# Sort input data
input_sorted = input_data.sort_values(by=['Store', 'Item']).reset_index(drop=True)
input_sorted['RowNumber'] = input_sorted.groupby('Store').cumcount() + 1

# Pivot table
pivot_table = input_sorted.pivot(index='Store', columns='RowNumber', values='Item')
pivot_table.columns = pivot_table.columns.astype(str)
pivot_table['string'] = pivot_table.apply(lambda x: '/'.join(x.dropna().astype(str)), axis=1)

# Merge data
result = pd.merge(input_sorted[['Store', 'RowNumber']], pivot_table, on='Store', how='left')
result['processed_string'] = result.apply(lambda x: x['string'][:(2 * x['RowNumber'] - 1)], axis=1)
result = result.rename(columns={'processed_string': 'Item'})
result = result[['Store', 'Item']]

# Rename columns in test data
test_data.rename(columns={'Store.1': 'Store', 'Item.1': 'Item'}, inplace=True)

# Check if result equals test data
print(result.equals(test_data)) # Output: 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

    • Uses direct pattern parsing where the workbook encodes logic in text

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