Forum Discussion

YYGG's avatar
YYGG
Regular Visitor
10 years ago
Solved

Unique records

I have a table with many columns, the relevant columns are Emp ID and Status. I want to find the number of unique employees from the rows in Status=Active In this example the answer should be ...
Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    10 years ago

    Hi YYGG,

     

    In your scenario, after importing data to desktop, you can modify the data model in Query Editor.

     

    1. Split the column ID based on comma(,).

     

    2. Select column Status, then check Unpivot Columns-> Unpivot Other Columns.

     

     

    3. Filter rows when Status is "Active".

     

    4. Set Group By "Value" and "Status".

    5. Final results.

     

    If you have any question, please feel free to ask.

     

    Best Regards,
    Qiuyun Yu

v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
10 years ago

Hi YYGG,

 

In your scenario, after importing data to desktop, you can modify the data model in Query Editor.

 

1. Split the column ID based on comma(,).

 

2. Select column Status, then check Unpivot Columns-> Unpivot Other Columns.

 

 

3. Filter rows when Status is "Active".

 

4. Set Group By "Value" and "Status".

5. Final results.

 

If you have any question, please feel free to ask.

 

Best Regards,
Qiuyun Yu

  • YYGG's avatar
    YYGG
    Regular Visitor
    10 years ago

    It took me some time to implement because I had some changes in the table and logic.

    But it assisted me a lot and worked great. Thanks.