Excel BI - PowerQuery Challenge 270

excel-challenges
power-query
Col1 Col2 Col3 Col4 RESULT PROBLEM
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 270

Challenge Description

Col1 Col2 Col3 Col4 RESULT PROBLEM

Solutions

library(tidyverse)
library(readxl)

path = "Power Query/PQ_Challenge_270.xlsx"
input = read_excel(path, range = "A1:D11") %>% as.matrix()
test  = read_excel(path, range = "F1:I11") %>% as.matrix()

output = map_dfc(1:nrow(input), ~input[.x, (seq_len(ncol(input)) + .x - 2) %% ncol(input) + 1]) %>% t()

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

  • Logic:

    • Reads the workbook range needed for the challenge

  • 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

path = "PQ_Challenge_270.xlsx"
input = pd.read_excel(path, usecols="A:D", nrows=11).values
test = pd.read_excel(path, usecols="F:I", nrows=11).values

output_matrix = np.array([input[i, (np.arange(input.shape[1]) + i) % input.shape[1]] for i in range(input.shape[0])])

print(np.allclose(output_matrix, test)) # 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.