#217–218

Puzzles

Author: ExcelBI

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

Puzzle #217

Turn around, transpose, pivot it all means almost the same. Like table asked us to spin itself around. Let get back to math maybe. We have table containing rate (maybe daily rate) for each person and units to be paid for. We need to reduce table but putting final values inside value cells, and then spin the table to have names from rows to columns, and months vice-versa. Final touch need to add both row and column totals. Get to the dancefloor now.

Loading libraries and data

library(tidyverse)
library(readxl)
library(janitor)

path = "Power Query/PQ_Challenge_217.xlsx"
input = read_excel(path, range = "A1:H5")
test  = read_excel(path, range = "J1:O8")

Trransformation

result = input %>%
  mutate(across(3:8, ~ . * Amt)) %>%
  select(-Amt) %>% 
  t() %>%
  as.data.frame() %>%
  row_to_names(1) %>%
  rownames_to_column(var = "Month")  %>%
  mutate(across(-Month, ~ as.numeric(.))) %>%
  adorn_totals(c("row", "col"))

Validation

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

Puzzle #218

We have bunch of projects that consists mainly of similar parts so we are tracking it in one table. Someone realise that we have completion date only for some of processes, so we need to qualify them into completed and not completed in finer visual version. Hurry up, management need it.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Power Query/PQ_Challenge_218.xlsx"
input = read_excel(path, range = "A1:C17")
test  = read_excel(path, range = "E1:G5") %>%
  replace_na(list(`Completed Tasks` = "", `Not Completed Tasks` = ""))

Transformation

result = input %>%
  mutate(has_date = ifelse(is.na(`Completion Date`), "Not Completed Tasks", "Completed Tasks")) %>%
  select(-`Completion Date`) %>%
  pivot_wider(names_from = has_date, values_from = Tasks, values_fn = list) %>%
  mutate(`Completed Tasks` = map_chr(`Completed Tasks`, ~paste(.x, collapse = ", ")),
         `Not Completed Tasks` = map_chr(`Not Completed Tasks`, ~paste(.x, collapse = ", ")))

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.