PowerQuery Puzzle solved with R

#181–182

Puzzles

Author: ExcelBI

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

Puzzle #181

This weekend we have very interesting situation, because we are basing on the same data, but different direction of activities. In first one we have stacked and pivoted table cut to section, and we need to make tidy data from it, and second one is going back to the same shape as original.

Going from untidy to tidy data is like changing from dr Jekyll to Mr Hyde, but the other way is exactly opposite, Mr Hyde transforms to its inferior version.

So first let’s make order from chaos.

Loading libraries and data

library(tidyverse)
library(openxlsx2)

file_path = "Power Query/PQ_Challenge_181.xlsx"

input = wb_read(file_path, col_names = FALSE, rows = 1:11, cols = "A:D")
test  = wb_read(file_path, col_names = TRUE, rows = 1:20, cols = "F:I")

Transformation

result = input %>%
  mutate(Date = ifelse(str_detect(A, "\\d"), A, NA)) %>%
  fill(Date) %>%
  set_names(c("Name", "Data1", "Data2", "Data3", "Date")) %>%
  pivot_longer(-c("Name","Date"), names_to = "Data", values_to = "Value") %>%
  mutate(Date = ifelse(str_detect(Date, ".*\\d{4}$"), mdy(Date), ymd(Date)) %>% as.Date(),
         Value = as.numeric(Value)) %>%
  na.omit() %>%
  select(2,1,3,4)

Validation

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

Puzzle #182

I don’t really like doing data transformation that way, but challenge is a challenge. Let’s try to make this monster, this Mr Hyde. Unlike opposite way, it need to make every section separately and then put it all together. But nothing is impossible… Sometimes you need more money, sometimes more time, sometimes time needed to discover the solution at all.

Loading libraries and data

library(tidyverse)
library(openxlsx2)

path = "Power Query/PQ_Challenge_182.xlsx"


input = wb_read(path, rows = 1:20, cols = "A:D")
test  = wb_read(path, rows = 1:11, cols = "F:I", col_names = FALSE, detect_dates = TRUE) %>%
  mutate(`F` = str_replace(`F`, "5/1/2014", "2014-05-01"))

Transformation

result = input %>%
  pivot_wider( names_from = "Data", values_from = "Value") %>%
  mutate(rn = row_number())

r1 = result %>%
  summarise(Name = format(Date), Data1 = "Data1", Data2 = "Data2", Data3 = "Data3", rn = 0, .by = Date) %>%
  distinct()

r2 = result %>%
  rbind(r1) %>%
  arrange(Date, rn) %>%
  select(-c(Date, rn))

colnames(r2) = colnames(test)

Validation

all.equal(r2, test, check.attributes = FALSE)
# [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.