cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

how to count unique values like product number that is produced in a row

Hi all,

 

i have a column with produced serial numbers, sorted ascending by timestamp. Now I want to count, how many of one unique product number was produced by the machine in a row, e.g. Product number ...437 was produced 4 times in a row, then number ...083 4 times in a row, and so on. Hope someone can help me with this issue, I created already an Index but I dont know how to continue..2020-12-14_09h48_00.png

 

 

1 ACCEPTED SOLUTION

Hello @Anonymous 

 

then use GroupKind.Local as 4th parameter. Here an example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1UElnZBIXOJAMhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product number" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product number", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product number"}, {{"Count", each Table.RowCount(_), type number}}, GroupKind.Local)
in
    #"Grouped Rows"

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

The final column should give me a specific count like this:

Product Number             Count

-437                                  

-437                                    2

-222

-222                                    2

-388                                    1

-437

-437

-437                                    3

Hello @Anonymous 

 

then use GroupKind.Local as 4th parameter. Here an example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1UElnZBIXOJAMhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product number" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product number", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product number"}, {{"Count", each Table.RowCount(_), type number}}, GroupKind.Local)
in
    #"Grouped Rows"

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

Jimmy801
Super User III
Super User III

Hello @Anonymous 

 

Group your table by serial number, and apply a count rows as a function.

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Anonymous
Not applicable

Hi Jimmy,

 

thank you for your advice. The problem is that I dont want the all-time total count of one product number, I need the "cluster structure" so if part -437 is produced 4 times directly in a row at 10a.m., the count should be 4. If part -437 will be produced again after 2 hours ( for example 2 times in a row) the counter should be 2 not total of 6.

 

AlB
Super User III
Super User III

Hi @Anonymous 

Please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. A screen cap doesn't allow people to readily copy the data and run a quick test and thus decreases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors