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.
Dear all,
I have a simple table with date colum and product, the products are manufactured by batches so I have several entries for product for diferents times range. I would like to know how many times a product is manufactured in a period of time, I put a example:
Product | Date | Rank expected |
A | 16/01/2020 22:56 | 1 |
A | 17/01/2020 22:41 | 1 |
A | 18/01/2020 6:36 | 1 |
C | 18/01/2020 12:25 | 1 |
C | 18/01/2020 20:20 | 1 |
C | 19/01/2020 20:05 | 1 |
C | 20/01/2020 3:59 | 1 |
C | 20/01/2020 11:54 | 1 |
D | 20/01/2020 12:57 | 1 |
D | 23/01/2020 12:09 | 1 |
D | 23/01/2020 20:03 | 1 |
D | 24/01/2020 3:58 | 1 |
D | 24/01/2020 11:52 | 1 |
D | 24/01/2020 19:47 | 1 |
D | 25/01/2020 3:41 | 1 |
A | 25/01/2020 5:34 | 2 |
A | 25/01/2020 13:29 | 2 |
A | 25/01/2020 21:24 | 2 |
A | 26/01/2020 5:18 | 2 |
B | 26/01/2020 10:22 | 1 |
B | 26/01/2020 18:17 | 1 |
B | 27/01/2020 2:12 | 1 |
A | 27/01/2020 3:55 | 3 |
D | 31/01/2020 20:18 | 2 |
D | 01/02/2020 4:13 | 2 |
C | 01/02/2020 6:04 | 3 |
C | 01/02/2020 13:59 | 3 |
C | 01/02/2020 21:54 | 3 |
C | 02/02/2020 5:49 | 3 |
C | 02/02/2020 13:44 | 3 |
C | 02/02/2020 21:38 | 3 |
C | 03/02/2020 5:33 | 3 |
A | 03/02/2020 12:55 | 2 |
A | 03/02/2020 20:51 | 2 |
A | 04/02/2020 4:46 | 2 |
A | 04/02/2020 12:41 | 2 |
A | 04/02/2020 20:37 | 2 |
A | 05/02/2020 4:31 | 2 |
Any idea or suggestion it will be apreciated,
Thanks in advanced
Solved! Go to Solution.
Hi @Muertepelá
that's a job for GroupKind.Local:
Please paste this code into the advanced editor and follow the steps:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdNLrsMwCAXQrVQZVypcwI6Zvc8uqu5/G3XaKuCKaY64BEju9+1nu27cbsQ3EOgCuLXtcf1Az6AcsJ9AzeVd8bcCw2EVgBwUMDJQVICih7iNCpjd9AX/XzDn6AGSgUYFR3MJ0KX5XsHRHCUM19TcclRaYgZz0QpYHKMCsCNVtBzF79f9XYHn2lHC7twD4uYEZ0SPvqzEzgGF8xI5djWfEj4V6iznBTM0J62Al5snwHnzFyCizHVUMKO0rJhRsgdIjhI5J09wfFdWwZzc4rSkeXJtFfDyRyWYUdIDLEfJrHg8AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product = _t, Date = _t]),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
#"Grouped Rows" = Table.Group(#"Added Index", {"Product"}, {{"Partition", each _, type table [Product=text, Date=text]}}, GroupKind.Local),
#"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Product"}, {{"Partition", each Table.AddIndexColumn(_, "Rank", 1,1)}}),
#"Expanded Partition" = Table.ExpandTableColumn(#"Grouped Rows1", "Partition", {"Partition", "Rank"}, {"Partition.1", "Rank"}),
#"Expanded Partition.1" = Table.ExpandTableColumn(#"Expanded Partition", "Partition.1", {"Date", "Index"}, {"Date", "Index"}),
#"Sorted Rows" = Table.Sort(#"Expanded Partition.1",{{"Index", Order.Ascending}})
in
#"Sorted Rows"
Used techniques:
in step "Grouped Rows": https://blog.crossjoin.co.uk/2014/01/03/aggregating-by-local-groups-in-power-query/
in step "Grouped Rows1": https://www.youtube.com/watch?v=-3KFZaYImEY
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@Muertepelá ,Is it based on product and month ?? Can you explain the logic
In between
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
HI
Sorry there was a mistake on the previus table, apologies¡¡¡¡ Here the right one¡¡¡
Product | Date | Rank expected |
A | 16/01/2020 22:56 | 1 |
A | 17/01/2020 22:41 | 1 |
A | 18/01/2020 6:36 | 1 |
C | 18/01/2020 12:25 | 1 |
C | 18/01/2020 20:20 | 1 |
C | 19/01/2020 20:05 | 1 |
C | 20/01/2020 3:59 | 1 |
C | 20/01/2020 11:54 | 1 |
D | 20/01/2020 12:57 | 1 |
D | 23/01/2020 12:09 | 1 |
D | 23/01/2020 20:03 | 1 |
D | 24/01/2020 3:58 | 1 |
D | 24/01/2020 11:52 | 1 |
D | 24/01/2020 19:47 | 1 |
D | 25/01/2020 3:41 | 1 |
A | 25/01/2020 5:34 | 2 |
A | 25/01/2020 13:29 | 2 |
A | 25/01/2020 21:24 | 2 |
A | 26/01/2020 5:18 | 2 |
B | 26/01/2020 10:22 | 1 |
B | 26/01/2020 18:17 | 1 |
B | 27/01/2020 2:12 | 1 |
A | 27/01/2020 3:55 | 3 |
D | 31/01/2020 20:18 | 2 |
D | 01/02/2020 4:13 | 2 |
C | 01/02/2020 6:04 | 2 |
C | 01/02/2020 13:59 | 2 |
C | 01/02/2020 21:54 | 2 |
C | 02/02/2020 5:49 | 2 |
C | 02/02/2020 13:44 | 2 |
C | 02/02/2020 21:38 | 2 |
C | 03/02/2020 5:33 | 2 |
A | 03/02/2020 12:55 | 4 |
A | 03/02/2020 20:51 | 4 |
A | 04/02/2020 4:46 | 4 |
A | 04/02/2020 12:41 | 4 |
A | 04/02/2020 20:37 | 4 |
A | 05/02/2020 4:31 | 4 |
I have a data set of thounsands, The table is just for near a month, and in that month product A was manufactured 4 times, producto B was manufactured only once, product C was manufactured 2 times and product D was 2. I would like to know how many times each product has been manufactured over time.
Thanks in advance
Why for product A 16, 17,18 is 1 are we checking the sequence, when it becomes 2 when it becomes 3
Hi
Rank column is that I would like to obtain. For first dates and product A, is 1, for next dates of product A, becomes in 2 , for next dates of product A becomes in 3 and so for each product. I have entries for each product when product changes and undeterminate time.
Thanks
Hi @Muertepelá
that's a job for GroupKind.Local:
Please paste this code into the advanced editor and follow the steps:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdNLrsMwCAXQrVQZVypcwI6Zvc8uqu5/G3XaKuCKaY64BEju9+1nu27cbsQ3EOgCuLXtcf1Az6AcsJ9AzeVd8bcCw2EVgBwUMDJQVICih7iNCpjd9AX/XzDn6AGSgUYFR3MJ0KX5XsHRHCUM19TcclRaYgZz0QpYHKMCsCNVtBzF79f9XYHn2lHC7twD4uYEZ0SPvqzEzgGF8xI5djWfEj4V6iznBTM0J62Al5snwHnzFyCizHVUMKO0rJhRsgdIjhI5J09wfFdWwZzc4rSkeXJtFfDyRyWYUdIDLEfJrHg8AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product = _t, Date = _t]),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
#"Grouped Rows" = Table.Group(#"Added Index", {"Product"}, {{"Partition", each _, type table [Product=text, Date=text]}}, GroupKind.Local),
#"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Product"}, {{"Partition", each Table.AddIndexColumn(_, "Rank", 1,1)}}),
#"Expanded Partition" = Table.ExpandTableColumn(#"Grouped Rows1", "Partition", {"Partition", "Rank"}, {"Partition.1", "Rank"}),
#"Expanded Partition.1" = Table.ExpandTableColumn(#"Expanded Partition", "Partition.1", {"Date", "Index"}, {"Date", "Index"}),
#"Sorted Rows" = Table.Sort(#"Expanded Partition.1",{{"Index", Order.Ascending}})
in
#"Sorted Rows"
Used techniques:
in step "Grouped Rows": https://blog.crossjoin.co.uk/2014/01/03/aggregating-by-local-groups-in-power-query/
in step "Grouped Rows1": https://www.youtube.com/watch?v=-3KFZaYImEY
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |