Excel BI - PowerQuery Challenge 269

excel-challenges
power-query
From City To City Result City1 City2 City1-City2
Published

March 24, 2026

Illustration for Excel BI - PowerQuery Challenge 269

Challenge Description

From City To City Result City1 City2 City1-City2

Solutions

library(tidyverse)
library(readxl)
library(igraph)

path = "Power Query/PQ_Challenge_269.xlsx"
input = read_excel(path, range = "A1:B15")
test  = read_excel(path, range = "D1:D13") %>% arrange(Result)

g = graph_from_data_frame(input, directed = TRUE)
all_paths = all_simple_paths(g, from = "City1", to = V(g))
all_paths_df = map_df(all_paths, ~{
  path = .x
  path_str = paste(V(g)[path]$name, collapse = "-")
  data.frame(path = path_str)
}) %>%
  arrange(path)

all.equal(all_paths_df$path, test$Result) # 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 networkx as nx

path = "PQ_Challenge_269.xlsx"
input = pd.read_excel(path, usecols="A:B", nrows=15)
test = pd.read_excel(path, usecols="D", nrows=12).sort_values(by="Result").reset_index(drop=True)

G = nx.from_pandas_edgelist(input, source='From City', target='To City', create_using=nx.DiGraph())
all_paths = ['-'.join(path) for target in G.nodes if target != 'City1' for path in nx.all_simple_paths(G, source='City1', target=target)]

df_paths = pd.DataFrame(all_paths, columns=['Path']).sort_values(by='Path').reset_index(drop=True)
print(df_paths['Path'].equals(test['Result'])) # 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.