CODEWITHSUNDEEP
rpowerbipowerquerypowerbi-embedded
Knight
Knight

Reputation: 363

Groupby in MS Power Query/Power BI for specific values

My data looks like 

    Name  country  Group   Date      Score
      a      UK    IT     18/11/2016    1
      a      UK    IT     19/11/2016   -1
      a      UK    IT     20/11/2016    2
      a      UK    IT     21/11/2016    2
      a      UK    IT     22/11/2016    NA
      a      UK    IT     23/11/2016    NA
      a      UK    IT     24/11/2016    NA
      a      UK    IT     25/11/2016    NA
      a      UK    IT     26/11/2016    NA
      b      UK    VK     18/11/2016    1
      b      UK    VK     19/11/2016   -1
      b      UK    VK     20/11/2016    1
      b      UK    VK     21/11/2016    1
      b      UK    VK     22/11/2016    1
      b      UK    VK     23/11/2016   -2
      b      UK    VK     24/11/2016    2
      b      UK    VK     25/11/2016    1
      b      UK    VK     26/11/2016   -1

I made a Group By operation averaging the score column based on 'Name', 'Country', 'Group' columns in power query(Power BI) then the data looks like 

   Name  country  Group   Average Score
      a      UK    IT        0.44
      b      UK    VK        0.33

But I'm trying to get average scores as "NA" when there are >=4 Na's in Score column for every person which may look like 

   Name  country  Group   Average Score
      a      UK    IT        NA
      b      UK    VK        0.33

I couldn't find solution using Group by directly, any ideas or suggestions? (Power BI also supports R, any transformations that can be done to achieve this?) Thanks in advance.

Upvotes: 1

Views: 155

Answers (1)

Nick Criswell
Nick Criswell

Reputation: 1743

This can be accomplished with dplyr as follows. 

library(dplyr)

df  <- read.table(text = "Name  country  Group   Date      Score
a      UK    IT     18/11/2016    1
a      UK    IT     19/11/2016   -1
a      UK    IT     20/11/2016    2
a      UK    IT     21/11/2016    2
a      UK    IT     22/11/2016    NA
a      UK    IT     23/11/2016    NA
a      UK    IT     24/11/2016    NA
a      UK    IT     25/11/2016    NA
a      UK    IT     26/11/2016    NA
b      UK    VK     18/11/2016    1
b      UK    VK     19/11/2016   -1
b      UK    VK     20/11/2016    1
b      UK    VK     21/11/2016    1
b      UK    VK     22/11/2016    1
b      UK    VK     23/11/2016   -2
b      UK    VK     24/11/2016    2
b      UK    VK     25/11/2016    1
b      UK    VK     26/11/2016   -1", 
                  header = TRUE)

pivot <- df %>%
  group_by(Name, country, Group) %>%
  summarise(avg_score = ifelse(sum(is.na(Score)) >= 4, NA, mean(Score)))

> pivot
Source: local data frame [2 x 4]
Groups: Name, country [?]

    Name country  Group avg_score
  <fctr>  <fctr> <fctr>     <dbl>
1      a      UK     IT        NA
2      b      UK     VK 0.3333333

Upvotes: 2

Related Questions