PowerQuery Puzzle solved with R

#157–158

Puzzles

Author: ExcelBI

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

Puzzle #157

We are getting long table of logs, and don’t really know what is going on here. But our manager wants to know when data in certain groups and variables changes. And nothing else should stay in table. Tough job, but who else will do it.

Loading libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Power Query/PQ_Challenge_157.xlsx", range = "A1:E31")
test  = read_excel("Power Query/PQ_Challenge_157.xlsx", range = "G1:K31") %>%
  mutate(across(everything(), as.character))

Transformation

log_changes <- function(data) {
  data %>%
    mutate(across(everything(), as.character)) %>%
    group_by(Group) %>%
    mutate(across(everything(),
                  ~if_else(lag(.x) != .x & !is.na(lag(.x)), .x, NA_character_))) %>%
    ungroup()
}

result = log_changes(input) 

Validation

identical(result, test)
# [1] TRUE

Wow! Much faster than we was thinking before.

Puzzle #158

I assume that almost all PQ puzzles are about cleaning data. And this time is again what we need to do. We have table with data about several people, but they are looking like different people wrote different parts. Some data are lacking, but we need to make it consistent in shape. And one more thing… we have two levels of column headers.

Load libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Power Query/PQ_Challenge_158.xlsx", range = "A1:K5", 
                   col_names = T, .name_repair = "unique") 
test  = read_excel("Power Query/PQ_Challenge_158.xlsx", range = "A10:G17") %>%
  mutate(across(everything(), as.character))

Transformation

r1 = input %>%
  pivot_longer(cols = -c(1), values_to = "value", names_to = "variable") %>%
  mutate(variable = if_else(str_starts(variable, "D"), variable, NA_character_)) %>%
  fill(variable, .direction = "down") %>%
  group_by(Dept) %>%
  nest()

headers = r1[[2]][[1]]$value

r2 = r1 %>%
  filter(Dept != "Group") %>%
  unnest(data) %>%
  mutate(headers = headers) %>%
  pivot_wider(names_from = headers, values_from = value) %>%
  filter(!is.na(`Emp ID`)) %>%
  select(Group = Dept, Dept = variable, everything()) %>%
  ungroup()

Validation

identical(r2, 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.