Solving PowerQuery Puzzles 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.

Continue reading: PowerQuery Puzzle solved with R

Analysis and Follow-Up on PowerQuery Puzzle Solving with R

The original content revolves around the reasoning and solution for two problems, Puzzle #183 and Puzzle #184, utilizing R language. The author demonstrates the process of how to load the libraries, initialize the input data, execute transformation operations, and validates results using the tidyverse and readxl R libraries. Moreover, the author solves these puzzles by creating performing formulas for compound percent and using regular expressions.

Long-Term Implications and Possible Developments

In the long run, these puzzle-solving exercises highlight the versatility and robustness of the R programming language in manipulating data and solving complex problems. They reveal the potential for more intricate problems to be solved using R, leveraging its extensive list of inbuilt libraries and packages.

The nature of these challenges underlines the potential for a shift towards utilizing more advanced programming languages in problem-solving, particularly those within data manipulation and transformation realms.

The texts’ complexity is likely to generate more challenges in a similar vein, thereby cultivating an expanded understanding and skill in data handling, and contributing to the growth of data science as a field.

Actionable Advice

Based on these findings, it’s advisable to invest time in understanding the logical structures and procedural flow involved in solving these puzzles. The comprehension of formula generation for compound percent and the use of regular expressions could be instrumental in solving data transformation problems.

It’s essential to go beyond these examples and practice manipulating different datasets in R. Continuous practice and engaging with the burgeoning data science community and blogs like ‘Numbers around us’ which offer similar conundrums will augment your skills.

Taking advantage of the numerous resources available to learn not just R programming but data science more broadly, like online courses, blogs and data science websites like R-bloggers would be fruitful.

Finally, it’s important to keep up-to-date with the development and updates within the R language and its libraries, as the language is constantly evolving to meet emerging data challenges.

Read the original article