Excel BI - PowerQuery Challenge 166

excel-challenges
power-query
Transpose the given table into result table. Sort is on the Company.
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 166

Challenge Description

Transpose the given table into result table. Sort is on the Company.

Solutions

library(tidyverse)
library(readxl)

input = read_excel("Power Query/PQ_Challenge_166.xlsx", range = "A1:C14")
test  = read_excel("Power Query/PQ_Challenge_166.xlsx", range = "E1:H5")

result = input %>%
  fill(`Tracking No`, .direction = "down") %>%
  mutate(group = cumsum(str_starts(`Tracking No`, pattern = "[A-Z]"))) %>%
  group_by(group) %>%
  summarise(`Tracking No` = paste0(unique(`Tracking No`), collapse = ", "),
            `Item Count` = n_distinct(`Items`, na.rm = TRUE) %>% as.numeric(),
            `Total Amount` = sum(`Amount`, na.rm = TRUE)) %>%
  select(-group) %>%
  separate(`Tracking No`, into = c("Company", "Trackng No"), sep = ", ") %>%
  mutate(`Trackng No` = as.numeric(`Trackng No`)) %>%
  ungroup() %>%
  arrange(Company)

test == result

  • Logic:

    • Reads the workbook range needed for the challenge

    • Aggregates or ranks values at the relevant grouping level

    • 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

input_data = pd.read_excel("PQ_Challenge_166.xlsx", usecols="A:C", nrows=14)
test = pd.read_excel("PQ_Challenge_166.xlsx", usecols="E:H", nrows=5)

result = input_data.copy()
result["Tracking No"] = result["Tracking No"].ffill()
result["group"] = result["Tracking No"].str.match(r"^[A-Z]").cumsum()
result = (
    result.groupby("group", as_index=False)
    .agg(
        TrackingNo=("Tracking No", lambda s: ", ".join(pd.unique(s))),
        ItemCount=("Items", lambda s: s.dropna().nunique()),
        TotalAmount=("Amount", lambda s: s.fillna(0).sum()),
    )
)
split = result["TrackingNo"].str.split(", ", expand=True)
result["Company"] = split[0]
result["Trackng No"] = pd.to_numeric(split[1])
result = result[["Company", "Trackng No", "ItemCount", "TotalAmount"]].rename(columns={"ItemCount": "Item Count", "TotalAmount": "Total Amount"}).sort_values("Company").reset_index(drop=True)

print((test == result).all().all())

  • Logic:

    • Reads the workbook range needed for the challenge

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