Excel BI - PowerQuery Challenge 366

excel-challenges
power-query
Transpose the table as shown. Introduce a new column named ‘Sub Order ID’ which is basically an Index column for a given Order ID.
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 366

Challenge Description

Transpose the table as shown. Introduce a new column named “Sub Order ID” which is basically an Index column for a given Order ID.

Solutions

library(tidyverse)
library(readxl)

path <- "Power Query/300-399/366/PQ_Challenge_366.xlsx"
input <- read_excel(path, range = "A1:B46")
test <- read_excel(path, range = "D1:J11")

result <- input %>%
  mutate(
    order_grp = cumsum(Attribute == "Order ID")
  ) %>%
  group_by(order_grp) %>%
  mutate(
    sub_id = cumsum(Attribute == "Customer")
  ) %>%
  ungroup() %>%
  group_by(order_grp, sub_id, Attribute) %>%
  fill(Value, .direction = "down") %>%
  ungroup() %>%
  pivot_wider(
    id_cols = c(order_grp, sub_id),
    names_from = Attribute,
    values_from = Value
  ) %>%
  arrange(order_grp, sub_id) %>%
  select(
    `Order ID`,
    `Sub Order ID` = sub_id,
    Customer,
    Date,
    Region,
    Priority,
    Status
  ) %>%
  fill(`Order ID`, .direction = "down") %>%
  filter(`Sub Order ID` != 0) %>%
  mutate(
    Date = janitor::excel_numeric_to_date(as.numeric(Date)) %>%
      as.POSIXct(origin = "1970-01-01", tz = "UTC")
  )

all.equal(result, test, check.attributes = FALSE)
# Sub Order ID in ORD-105 should be 1 not 2.

  • 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

path = "Power Query/300-399/366/PQ_Challenge_366.xlsx"
input = pd.read_excel(path, usecols="A:B", nrows=46)
test = pd.read_excel(path, usecols="D:J", nrows=10).fillna("")

result = (
    input
    .assign(order_grp=lambda d: (d["Attribute"] == "Order ID").cumsum())
    .assign(sub_id=lambda d: d.groupby("order_grp")["Attribute"]
            .transform(lambda s: (s == "Customer").cumsum()))
    .pivot_table(
        index=["order_grp", "sub_id"],
        columns="Attribute",
        values="Value",
        aggfunc="first"
    )
    .reset_index()
    .sort_values(["order_grp", "sub_id"])
    .rename(columns={"sub_id": "Sub Order ID"})
    .loc[:, ["Order ID", "Sub Order ID", "Customer", "Date", "Region", "Priority", "Status"]]
    .assign(**{"Order ID": lambda d: d["Order ID"].ffill().astype(str)})
    .query("`Sub Order ID` != 0")
    .fillna("")
    .reset_index(drop=True)
)

print(result.equals(test))
# Sub Order ID in ORD-105 should be 1 not 2.

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