Group by columns on Power BI

Question

I have column in which 3-4 different values and data like this

companyname   servicestatus  employee
abc           inservice       abc_e1
abc           inservice       abc_ew1
abc           resigned        abc_2
abc           retired         abc_3
abc           retired         abc_23
def           inservice       def_12
def           inservice       def_13
def           inservice       def_14
def           resigned        def_3
def           resigned        def_4
def           resigned        def_5
def           retired         def_3

so currently this show data like this

companyname   servicestatus  count
    abc         inservice      2
    abc         resigned       1
    abc         retired        2
    def         inservice      3
    def         resigned       3
    def         retired        1

so on Power BI I want to show like this

companyname   joiners    leavers
abc              2         3
def              3         4

means in joiners I want to show only inservice but on leavers I want to show resigned and retired both

How do I do this in Power BI

Answer 1

Starting from the top table, add a Custom Column (under the Add Column tab).

= if [servicestatus] = "inservice" then "joiners" else "leavers"

Then group by [companyname] and the new [Custom] column.

Finally, select [Custom] column and choose Pivot Column under the Transform tab. Choose Count as the Values Column on the drop down.

Answer 2

One method using PowerBI desktop is as follows:

1. Add the necessary tables to the data model using "Get Data"
2. Setup the relationships from the designer, if needed
3. To create the grouping, click on Edit Queries and choose Group By
4. Add Group by selecting appropriate tables and then click Close & apply button

Answer 3

Before Grouping:

After Grouping:

= Table.Group(#"Renamed Columns", {"type Num of active days"}, {{"Fitness Distinct users", each List.Sum([Distict Users]), type number}, {"Number of events", each List.Sum([Number of Events]), type number}})