PowerQuery Puzzle solved with R

#229–230

Puzzles

Author: ExcelBI

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

Puzzle #229

When somebody is working on a project for more than a month it doesn’t really matter for him individually. But accountants wouldn’t be happy if we throw cost into interval which is not month. So we need to asssign amount to each month of its duration respectively and proportionally. Find out how to do it.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Power Query/PQ_Challenge_229.xlsx"
input = read_excel(path, range = "A1:D6")
test  = read_excel(path, range = "F1:H16")

Transformation

result = input %>%
  mutate(days = as.numeric(as.Date(`To Date`) - as.Date(`From Date`)) + 1, 
         daily = Amount / days) %>%
  rowwise() %>%
  mutate(date = list(seq(`From Date`, `To Date`, by = "day"))) %>%
  unnest(date) %>%
  mutate(`Month - Year` = paste0(str_pad(month(date), width = 2, "0", side = "left"), "-", str_sub(year(date), 3, 4))) %>%
  summarise(Amount = round(sum(daily),0), .by = c(Transaction, `Month - Year`))

Validation

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

Puzzle #230

We have summary of sales amounts from whole world, but it looks like calendar chart with square for each month. We need to summarise it “math way”. So first tidy up this table, then stack months into one two-column structure, again tidy and finally summarise them all together into months. Little mess, I know, but it is very fast job.

Loading libraries and data

library(tidylibrary(tidyverse)
library(readxl)

path = "Power Query/PQ_Challenge_229.xlsx"
input = read_excel(path, range = "A1:D6")
test  = read_excel(path, range = "F1:H16")

Transformation

result = input %>%
  mutate(days = as.numeric(as.Date(`To Date`) - as.Date(`From Date`)) + 1, 
         daily = Amount / days) %>%
  rowwise() %>%
  mutate(date = list(seq(`From Date`, `To Date`, by = "day"))) %>%cscscsssssssssssssssssssssssssssssSCsc
  unnest(date) %>%
  mutate(`Month - Year` = paste0(str_pad(month(date), width = 2, "0", side = "left"), "-", str_sub(year(date), 3, 4))) %>%
  summarise(Amount = round(sum(daily),0), .by = c(Transaction, `Month - Year`))

Validation

all.equal(result, 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.

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.