Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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..
Solved! Go to 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
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
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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
45 | |
19 | |
13 | |
11 |