Excel BI - PowerQuery Challenge 342

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

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 342

Challenge Description

Group1 Group2 Transpose the table as shown.

Solutions

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

path <- "Power Query/300-399/342/PQ_Challenge_342.xlsx"
input <- read_excel(path, range = "A1:F19")
test  <- read_excel(path, range = "H1:k13")

roll_left <- \(x) c(x[-1], x[1])

left_shift <- \(r) {
  while(r[1] == "" || is.na(r[1])) r <- roll_left(r)
  r
}

df <- input %>%
  split(1:nrow(input)) %>%
  map(~ left_shift(unlist(.x))) %>%
  map_dfr(~ set_names(.x, names(input))) %>%
  remove_empty("cols") 

r1 = df %>%
  filter(row_number() %% 2 == 1) %>%
  select(Group1 = Col1, Group2 = Col2)

r2 = df %>%
  filter(row_number() %% 2 == 0) %>%
  select(Value1 = Col1, Value2 = Col2) %>%
  mutate(across(everything(), as.numeric))

res = bind_cols(r1, r2) %>%
  add_row(Group1 = NA, Group2 = NA, Value1 = NA, Value2 = NA, .before = 4) %>%
  add_row(Group1 = NA, Group2 = NA, Value1 = NA, Value2 = NA, .before = 7) %>%
  add_row(Group1 = NA, Group2 = NA, Value1 = NA, Value2 = NA, .before = 11)

all.equal(res, 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
import numpy as np
from openpyxl import load_workbook

path = "Power Query/300-399/342/PQ_Challenge_342.xlsx"
input_df = pd.read_excel(path, usecols="A:F", nrows=18)
test_df  = pd.read_excel(path, usecols="H:K", nrows=12)

def roll_left(x):
    return x[1:] + [x[0]]

def left_shift(r):
    while r[0] == "" or pd.isna(r[0]):
        r = roll_left(r)
    return r

df = pd.DataFrame([left_shift(row.tolist()) for _, row in input_df.iterrows()], columns=input_df.columns)
df = df.dropna(axis=1, how='all').loc[:, (df != "").any(axis=0)]

res = pd.concat([
    df.iloc[::2][["Col1", "Col2"]].rename(columns={"Col1": "Group1", "Col2": "Group2"}).reset_index(drop=True),
    df.iloc[1::2][["Col1", "Col2"]].rename(columns={"Col1": "Value1", "Col2": "Value2"}).apply(pd.to_numeric, errors='coerce').reset_index(drop=True)
], axis=1)

for idx in [3, 6, 10]:
    res = pd.concat([res.iloc[:idx], pd.DataFrame([[np.nan]*4], columns=res.columns), res.iloc[idx:]], ignore_index=True)

print(res.equals(test_df))  # True

  • Logic:

    • Reads the workbook range needed for the challenge

    • Applies the rule iteratively until the output is complete

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