Unique records

[YYGG]

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 7 (10000, 10001, 10002, 10003, 10004, 10005)

Is it possible?

[v-qiuyu-msft · Accepted solution]

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]

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.

[radpir]

hi YYGG,

you didn't specify what tool you want to use to get the results.

i created a simple Power Query query that gets you the list of values as per your example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQUTA0VNJRckwuySxLVYrVAYoZAsWMgNgYTdwElQ/kQcRBak3R1QLFzMAqYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Status = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Status", type text}}),
    AddColumn = Table.AddColumn(ChangedType, "Custom", each Text.Split([ID], ", ")),
    ExpandColumn = Table.ExpandListColumn(AddColumn, "Custom"),
    RemoveColumnID = Table.RemoveColumns(ExpandColumn,{"ID"}),
    FilterActive = Table.SelectRows(RemoveColumnID, each ([Status] = "Active")),
    RemoveDuplicates = Table.Distinct(FilterActive)
in
    RemoveDuplicates

the first step just creates the sample table so you can ignore it and replace it with your own table.

the rest of the script should work ok.

radpir