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

[Anonymous]

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..

[Anonymous]

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

[Jimmy801 · 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

[Jimmy801]

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]

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]

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