PowerQuery Puzzle solved with R

#143–144

Puzzles

Author: ExcelBI

From this week all files (xlsx with puzzle and R with solution) for each and every puzzle are available on my Github. Enjoy.

Suprisingly this week we have no time-related puzzles, they are rather transformations of tables.

Puzzle #143

In this puzzle we have some kind of log. Employees are entering values of observation, but some observation has the same value. To check if it is right or wrong we have to find duplicated values. But only duplicated, so that means do not note first occurence but only second (first duplicated) and last occurence. I know maybe it is little bit chaotic, but lets do it…

Load libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Power Query/PQ_Challenge_143.xlsx", range = "A1:C21")
test  = read_excel("Power Query/PQ_Challenge_143.xlsx", range = "F1:H7")

Transformation

result = input %>%
  group_by(Emp, Value) %>%
  mutate(rn = row_number()) %>%
  filter(rn == 2 | (rn == max(rn) & rn > 2)) %>%
  select(-rn) %>%
  ungroup()

# Pretty easy, isn't it?

Validation

identical(result, test)
#> [1] TRUE

Puzzle #144

Again we have some employee’s log. They forget to note which of two days they log information about, so as work around manager told analyst to break log of each person in the middle (if there are odd number of logs, “first half” should be bigger). So let correct somebody’s mistakes.

Load libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Power Query/PQ_Challenge_144.xlsx", range = "A1:B16")
test  = read_excel("Power Query/PQ_Challenge_144.xlsx", range = "E1:G16")

Transformation

result = input %>%
  group_by(Group) %>%
  mutate(Half = ifelse(row_number() <= ceiling(n()/2), "First", "Second")) %>%
  ungroup() %>%
  group_by(Group, Half) %>%
  mutate(`Running Total` = cumsum(Value)) %>%
  ungroup() %>%
  select(-Half)

Validation

identical(result, test)
#> [1] TRUE

Thanks for your engagement, and let me know if you have any comments.

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.