Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
YYGG
Regular Visitor

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

uniqueEmps.png

In this example the answer should be 7 (10000, 10001, 10002, 10003, 10004, 10005)

Is it possible?

1 ACCEPTED SOLUTION
v-qiuyu-msft
Community Support
Community Support

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(,).

x7.PNG

 

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

 

x1.PNGx2.PNG

 

3. Filter rows when Status is "Active".

x3.PNG

 

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

x5.PNG

5. Final results.

x6.PNG

 

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

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-qiuyu-msft
Community Support
Community Support

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(,).

x7.PNG

 

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

 

x1.PNGx2.PNG

 

3. Filter rows when Status is "Active".

x3.PNG

 

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

x5.PNG

5. Final results.

x6.PNG

 

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

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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
Resolver II
Resolver II

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.