PowerQuery Puzzle solved with R

#195–196

Puzzles

Author: ExcelBI

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

Puzzle #197

Today we have some warehouse management issue. We were provided with log of products stocked in three stores in three months. You would say, Nice, but we don’t have any stock levels in there just INs and OUTs. We have to fix it and find Start and End Stock for each move of products assuming that we started on January. Try to find nice way to do it.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Power Query/PQ_Challenge_197.xlsx"

input = read_xlsx(path, range = "A1:E21")
test  = read_xlsx(path, range = "H1:N21")

Transformation

result <- input %>%
  group_by(Item, Store) %>%
  mutate(data = accumulate(`Stock IN` - `Stock OUT`, `+`),
         `Start Stock` = lag(data, default = first(`Stock IN`)),
         `End Stock` = data) %>%
  ungroup() %>%
  select(-data) 

Validation

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

Puzzle #198

I don’t know if scenario like for today is really used in any industry, but anyway it is nice case to show how we manipulate data. We need to find max value per month, and then for each row in month, place cumulative sum of those maxes. I used little self join here, but of course it can be done many other ways as well.

Loading libraries and data

library(readxl)
library(tidyverse)

path  = "Power Query/PQ_Challenge_198.xlsx"

input = read_excel(path, range = "A1:B20")
test  = read_excel(path, range = "D1:F20")

Transformation

result = input %>%
  mutate(month = month(Date)) %>%
  summarise(Max = max(Value), .by = month) %>%
  mutate(`Running Total` = cumsum(Max)) %>%
  right_join(input %>% mutate(month = month(Date)), by = "month") %>%
  select(Date, Value, `Running Total`)

Validation

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