PowerQuery Puzzle solved with R

#183–184

Puzzles

Author: ExcelBI

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

Puzzle #183

This Saturday we had quite interesting case to solve. We had table with rental agreements, which need to be transformed to kind of payment schedule. We have lenght of contract, interest rate increase after first year, and so on. It is one of cases where we can use formula for compound percent. Check it out.

Loading libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Power Query/PQ_Challenge_183.xlsx", range = "A1:F5")
test  = read_excel("Power Query/PQ_Challenge_183.xlsx", range = "H1:K24") %>%
  mutate(Rental = as.integer(Rental))

Transformation

result = input %>%
  unite("OYQ", Year, Quarter, sep = " ") %>%
  mutate(OYQ = yq(OYQ)) %>%
  rowwise() %>%
  mutate(quarters = list(seq.Date(from = as.Date(OYQ), by = "quarter", length.out = `Total Periods`))) %>%
  ungroup() %>%
  unnest(quarters) %>%
  mutate(Year = year(quarters), 
         Quarter = paste0("Q",quarter(quarters)),
         rn = row_number(),
         roll_year = (rn - 1) %/% 4 ,
         .by = Vendor) %>%
  mutate(Rental = round(Rental * (1 + `% Hike Yearly`/100)^roll_year) %>% as.integer()) %>%
  select(Vendor, Year, Quarter, Rental) 

Validation

identical(result, test)
# [1] TRUE

Puzzle #184

Sunday with Regex… good mind workout. Today we have some strings. And inside them suppose to be sequence as follow: Letters followed by digits. Sometimes there are more then one of such sequences, sometimes there are not even one. So we have to take last possible sequence from given string and concatenate them together inside the group. Conditional structures need to be used as well. Lets do it.

Loading libraries and data

library(tidyverse)
library(readxl)

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

Transformation

result = input %>%
  mutate(group = str_extract_all(Text,"[A-Za-z]+\\d+")) %>%
  mutate(group = map_chr(group, ~if(length(.x) > 1) tail(.x, 1) else if(length(.x) == 0) NA_character_ else .x)) %>%
  summarise(
      Text = paste(group[!is.na(group)], collapse = "-"),
      `Original Count` = n() %>% as.numeric(),
      `New Count` = sum(!is.na(group)) %>% as.numeric(),
      .by = Set
      )

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.