Excel BI - PowerQuery Challenge 192

Convert the problem table into result table.

Illustration for Excel BI - PowerQuery Challenge 192

Challenge Description

Convert the problem table into result table.

Solutions

R Solution

library(tidyverse)
library(readxl)

path = 'Power Query/PQ_Challenge_192.xlsx'
input = read_excel(path, range = "A1:E14")
test  = read_excel(path, range = "G1:J6")

count_workdays <- function(from, to) {
  map2(from, to, seq, by = "days") %>% 
    map(~ tibble(timeperiod = .x)) %>%
    map(~ mutate(.x, weekday = wday(timeperiod, week_start = 1)))  %>%
    map(~ filter(.x, weekday %in% 1:5)) %>%
    map_int(~ nrow(.x))
}

result = input %>%
  filter_all(any_vars(!is.na(.))) %>%
  fill(everything(), .direction = "down") %>%
  rename("scenario" = 3) %>%
  pivot_wider(names_from = scenario, values_from = c(4, 5)) %>%
  mutate(`Schedule Performance` = case_when(
    `To Date_Actual` > `To Date_Plan` ~ "Overrun",
    `To Date_Actual` < `To Date_Plan` ~ "Underrun",
    TRUE ~ "On Time"
  ),
  `Actual Dates` = map2_int(`From Date_Actual`, `To Date_Actual`, count_workdays) ,
  `Plan Dates` = map2_int(`From Date_Plan`, `To Date_Plan`, count_workdays),
  `Cost Performance` = case_when(
    `Actual Dates` > `Plan Dates` ~ "Overrun",
    `Actual Dates` < `Plan Dates` ~ "Underrun",
    TRUE ~ "At Cost"
  )) %>%
  mutate(nr = row_number(), .by = Project) %>%
  select(Project, Phase, nr, `Schedule Performance`, `Cost Performance`) %>%
  mutate(Project = if_else(nr == 1, Project, NA_character_)) %>%
  select(-nr)

identical(result, test)
# [1] TRUE

R Analysis

• Logic:

  • Reads the workbook range needed for the challenge

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

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

Python Solution

import pandas as pd

path = 'PQ_Challenge_192.xlsx'
input = pd.read_excel(path, usecols="A:E", nrows = 13)
test = pd.read_excel(path, usecols="G:J", nrows = 5)
test.columns = test.columns.str.replace(r'\.\d+', '', regex=True)

def count_workdays(from_date, to_date):
    from datetime import datetime, timedelta
    count = 0
    while from_date <= to_date:
        if from_date.weekday() < 5:
            count += 1
        from_date += timedelta(days=1)
    return count

result = input.copy()
result = result.dropna(how='all').fillna(method='ffill')
result.columns = ['Project', 'Phase', 'Scenario', 'From Date', 'To Date']
result = result.pivot_table(index=['Project', 'Phase'], columns='Scenario', values=['From Date', 'To Date'], aggfunc='first')
result['Schedule Performance'] = result.apply(lambda x: 'Overrun' if x['To Date']['Actual'] > x['To Date']['Plan'] else 'Underrun' if x['To Date']['Actual'] < x['To Date']['Plan'] else 'On time', axis=1)
result['Actual Workdays'] = result.apply(lambda x: count_workdays(x['From Date']['Plan'], x['To Date']['Actual']), axis=1)
result['Plan Workdays'] = result.apply(lambda x: count_workdays(x['From Date']['Plan'], x['To Date']['Plan']), axis=1)
result = result[['Schedule Performance', 'Actual Workdays', 'Plan Workdays']]
result.columns = result.columns.droplevel(1)
result['Cost Performance'] = result.apply(lambda x: 'Overrun' if x['Actual Workdays'] > x['Plan Workdays'] else 'Underrun' if x['Actual Workdays'] < x['Plan Workdays'] else 'At Cost', axis=1)
# remove columns actual workdays and plan workdays  
result = result.drop(columns=['Actual Workdays', 'Plan Workdays'])

print(result)
print(test)

Python

• Logic:

  • Reads the workbook range needed for the challenge

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

  • 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 moderate:

• It combines reshaping, grouping, or parsing steps that are common in Power Query style problems.

• The main challenge is reproducing the workbook output structure exactly.

Related Challenges

Explore more challenges from the same series:

• Excel BI - PowerQuery Challenge 191