Excel BI - PowerQuery Challenge 171

excel-challenges
power-query
Transpose given problem table into result table. Basically, it is taking 1st and 4th columns stacked on 2nd and 5th columns stacked on 3rd and 6th column. Repeat the same for all rows.If both column pairs are blanks/null, then skip that.
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 171

Challenge Description

Transpose given problem table into result table. Basically, it is taking 1st and 4th columns stacked on 2nd and 5th columns stacked on 3rd and 6th column. Repeat the same for all rows.If both column pairs are blanks/null, then skip that.

Solutions

library(tidyverse)
library(readxl)

input = read_excel("Power Query/PQ_Challenge_171.xlsx", range = "A1:F7")
test  = read_excel("Power Query/PQ_Challenge_171.xlsx", range = "H1:I15")

result = Map(function(c1, c4, c2, c5, c3, c6) list(c(c1, c4), c(c2, c5), c(c3, c6)), 
                    input$Col1, input$Col4, input$Col2, input$Col5, input$Col3, input$Col6) %>%
  unlist(recursive = F) %>%
  Map(function(x) list(x[1], x[2]), .) %>%
  tibble(Col = .) %>%
  unnest_wider(Col, names_sep = "") %>%
  filter(!(is.na(Col1) & is.na(Col2)))

identical(result, test)
#> [1] TRUE


# second solution - with purrr::pmap

r1 = input %>%
  transmute(
    Col = pmap(
      list(Col1, Col4, Col2, Col5, Col3, Col6),
      ~list(c(..1, ..2), c(..3, ..4), c(..5, ..6))
    )
  ) %>%
  unnest(cols = Col) %>%
  unnest_wider(Col, names_sep = "") %>%
  filter(!(is.na(Col1) & is.na(Col2)))

identical(r1, test)
#> [1] TRUE

  • Logic:

    • Reads the workbook range needed for the challenge

    • Reshapes the data into the structure required by the result table

  • 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 = pd.read_excel("PQ_Challenge_171.xlsx", usecols="A:F", nrows = 6)
test  = pd.read_excel("PQ_Challenge_171.xlsx", usecols="H:I", nrows = 15)
test = test.rename(columns = {"Col1.1": "Col1", "Col2.1": "Col2"})

input["M"] = list(zip(zip(input["Col1"], input["Col4"]), zip(input["Col2"], input["Col5"]), zip(input["Col3"], input["Col6"])))
input = input.explode("M")
input = input.pop("M").apply(pd.Series).reset_index(drop=True)
input.columns = ["Col1", "Col2"]
input = input.dropna(subset=["Col1", "Col2"], how="all").reset_index(drop=True)

print(test.equals(input)) # True

  • Logic:

    • Reads the workbook range needed for the challenge

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