PowerQuery Puzzle solved with R

#179–180

Puzzles

Author: ExcelBI

All files (xlsx with puzzle and R with solution) for each and every puzzle are available on my Github. Enjoy.

Puzzle #179

I must admit that I am not a big fan of baseball, but it doesn’t stop me before this challenge. As usual we have to transform table little bit, and make some kind of summary. So let bat swings.

Loading data and libraries

library(tidyverse)
library(readxl)

input = read_excel("Power Query/PQ_Challenge_179.xlsx", range = "A1:C10")
test  = read_excel("Power Query/PQ_Challenge_179.xlsx", range = "E1:K4")

Transformation

r1 = input %>%
  select(-`Runs Scored`) %>%
  mutate(player = paste0("Player",row_number()), .by = Team) %>%
  pivot_wider(names_from = player, values_from = Player)

r2 = input %>%
  mutate(max = max(`Runs Scored`), .by = Team) %>%
  filter(`Runs Scored` == max) %>%
  summarise(`Highest Scoring Player` = paste0(Player, collapse = ", "),
            `Highest Score` = unique(`Runs Scored`), .by = Team)

result = r1 %>%
  left_join(r2, by = "Team")

Validation

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

Puzzle #180

This time we need to find out who and when has the largest Month over Month difference in sales (both negative or positive). So we need to use some lags here. Find out.

Loading libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Power Query/PQ_Challenge_180.xlsx", range = "A1:B28")
test  = read_excel("Power Query/PQ_Challenge_180.xlsx", range = "D1:G4")

Transformation

result = input %>%
  mutate(Emp = ifelse(is.na(Sales), `Emp-Month`, NA_character_)) %>%
  fill(Emp) %>%
  filter(!is.na(Sales)) %>%
  mutate(lag_sales = lag(Sales, 1, default = 0),
         lag_month = lag(`Emp-Month`, 1, default = ""),
         total = sum(Sales), 
         change = abs(lag_sales - Sales),
         max_change = max(change),
         .by = Emp) %>%
  filter(change == max_change) %>%
  select(Emp, `Total Sales` = total, `Max Sales Change` = max_change, lag_month, `Emp-Month`) %>%
  unite("From - To Months", lag_month, `Emp-Month`, sep = " - ")

Validation

identical(result, test)
# [1] TRUE

Feel free to comment, share and contact me with advices, questions and your ideas how to improve anything. Contact me on Linkedin if you wish as well.

PowerQuery Puzzle solved with R was originally published in Numbers around us on Medium, where people are continuing the conversation by highlighting and responding to this story.