Excel BI - PowerQuery Challenge 273

excel-challenges
power-query
Transpose the table as shown.
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 273

Challenge Description

Transpose the table as shown.

Solutions

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

path = "Power Query/PQ_Challenge_273.xlsx"
input = read_excel(path, range = "A1:B20")
test  = read_excel(path, range = "D1:F18")

result = input %>%
  mutate(Store = ifelse(Data1 == "Store", Data2, NA)) %>%
  fill(Store) %>%
  filter(Data1 != "Store") %>%
  mutate(`Visit Date` = ifelse(Data1 == "Visit Date", Data2, NA)) %>%
  fill(`Visit Date`, .direction = "up") %>%
  filter(Data1 != "Visit Date") %>%
  mutate(`Visit Date` = excel_numeric_to_date(as.numeric(`Visit Date`)) %>% as.POSIXct()) %>%
  separate_rows(Data2, sep = ", ") %>%
  select(Store, Customer = Data2, `Visit Date`) 

all.equal(result, test, check.attributes = FALSE)
#> [1] TRUE

  • Logic:

    • Reads the workbook range needed for the challenge

    • 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 = "PQ_Challenge_273.xlsx"
input = pd.read_excel(path, sheet_name=0, usecols="A:B", nrows=20)
test = pd.read_excel(path, sheet_name=0, usecols="D:F", nrows=17)

input.columns = ["data1", "data2"]
input["store"] = input["data2"].where(input["data1"] == "Store").ffill()
input["visit_date"] = input["data2"].where(input["data1"] == "Visit Date").bfill()
input = input[~input["data1"].isin(["Store", "Visit Date"])]

result = (
    input.assign(data2=input["data2"].str.split(", "))
    .explode("data2")[["store", "data2", "visit_date"]]
    .rename(columns={"data2": "Customer", "store": "Store", "visit_date": "Visit Date"})
    .reset_index(drop=True)
)

print(result.equals(test)) # True

  • Logic:

    • Reads the workbook range needed for the challenge

    • 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 easy to moderate:

  • The transformation rule is readable, but the final layout still requires a careful implementation.