Excel BI - PowerQuery Challenge 307

excel-challenges
power-query
Country Alpha-3 Code Numeric Answer Albania ALB
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 307

Challenge Description

Country Alpha-3 Code Numeric Answer Albania ALB

Solutions

library(tidyverse)
library(readxl)
library(charcuterie)

path = "Power Query/300-399/307/PQ_Challenge_307.xlsx"
input = read_excel(path, range = "A1:C249")
test  = read_excel(path, range = "E1:E2") %>% pull()

result = input %>%
  mutate(
    sort_letter = map_chr(`Alpha-3 Code`, ~ paste0(sort(chars(.x)), collapse = "")),
    sort_digits = map_chr(Numeric, ~ paste0(sort(chars(.x)), collapse = ""))
  )

r1 <- result %>% filter(sort_letter %in% names(which.max(table(sort_letter))))
r2 <- result %>% filter(sort_digits %in% names(which.max(table(sort_digits))))

rf = intersect(r1$Country, r2$Country)

rf == test
# [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 = "300-399/307/PQ_Challenge_307.xlsx"
input = pd.read_excel(path, usecols="A:C", nrows=248, dtype={'Numeric': str})
test = pd.read_excel(path, usecols="E", nrows=1).iloc[:, 0].tolist()

def get_max_group(df, col):
    s = df[col].apply(lambda x: ''.join(sorted(str(x))))
    return df[s.isin(s.value_counts()[lambda x: x == x.max()].index)]

result = pd.merge(get_max_group(input, 'Alpha-3 Code'), 
                  get_max_group(input, 'Numeric'))['Country'].values
print(result == test) # 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.