PowerQuery Puzzle solved with R

#215–216

Puzzles

Author: ExcelBI

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

Puzzle #215

Today we have to do something that I really don’t like to do: constructing tables with esthetical structure, when esthethic is primarly for person reading and not having any knowledge bringing purpose. But today I found one purpose. After short transformation I decided to use gt package to do exactly what it have to do: make tables for people, not for machines. :)

Loading libraries and data

library(tidyverse)
library(readxl)
library(gt)

path = "Power Query/PQ_Challenge_215.xlsx"
input = read_excel(path, range = "A1:E20")
test  = read_excel(path, range = "G1:J15")

Transformation

result = input %>%
  mutate(out_day = case_when(
    !is.na(`Paid Date`) ~ NA_real_,
    `Due Date` > today() ~ 0,
    TRUE ~ as.numeric(difftime(today(), `Due Date`, units = "days"))
  )) %>%
  filter(!is.na(out_day)) %>%
  arrange(`Branch ID`, Customer, `Due Date`) %>%
  select(-`Paid Date`) %>%
  group_by(`Branch ID`)  %>%
  gt() %>%
  # change column names
  cols_label(Customer = "Branch ID / Customer",
             `Due Date` = "Due Date",
            `Loan Amt` = "Total Loan Amount",
             out_day = "Total Outstanding Days")

Presentation

Puzzle #216

That was pretty nice and brain warming puzzle unless I shouted Eureka! First look and I realized that have some pivoting and mutating, just normal job for data enthusiast. And something come to my mind just few minutes later. Simple transposition — did the job.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Power Query/PQ_Challenge_216.xlsx"
input = read_excel(path, range = "A1:E6")
test  = read_excel(path, range = "A11:E16")

Transformation — pivoting etc.

result = input %>%
  pivot_longer(everything(), names_to = "Column", values_to = "Item") %>%
  mutate(Column = str_remove(Column, "Column"), 
         item_n = str_remove(Item, "Item") %>% as.numeric()) %>%
  arrange(Column) %>%
  mutate(rn = row_number(), .by = Column) %>%
  mutate(Column_label = paste0("Items ", min(item_n, na.rm = TRUE), " - ", max(item_n, na.rm = TRUE)), .by = rn) %>%
  select(Column_label, Item, Column) %>%
  pivot_wider(names_from = Column_label, values_from = Item) %>%
  select(-Column)

Transformation — transposing

result = t(input)
result = as.data.frame(result)
names(result) = names(test)

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.