% group_by(Emp, Value) %>% mutate(rn = row_number()) %>% filter(rn == 2 | (rn == max(rn) & rn > 2)) %>% select(-rn) %>% ungroup()# Pretty easy, isn't it?Validationidentical(result, test)#> [1] TRUEPuzzle #144Again 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 datalibrary(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")Transformationresult = input %>% group_by(Group) %>% mutate(Half = ifelse(row_number() % ungroup() %>% group_by(Group, Half) %>% mutate(`Running Total` = cumsum(Value)) %>% ungroup() %>% select(-Half)Validationidentical(result, test)#> [1] TRUEThanks 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." />
[This article was first published on Numbers around us - Medium, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

#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.

To leave a comment for the author, please follow the link and comment on their blog: Numbers around us - Medium.

R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

# Never miss an update! Subscribe to R-bloggers to receive e-mails with the latest R posts.(You will not see this message again.)

Click here to close (This popup will not appear again)