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 there,
I have a 'collect key' column and want to provide a number (in sequence) next to it for all trips that have the same key, and for this to repeat for all of the different keys.
What this is showing is a transport companies trip details.
i.e - "key = xxx" the first-row occurrence would return 1, the second 2, third 3
- "key =yyy" the first-row occurrence would be 1, second 2 ...etc.
I guess what I am after is a cumulative count for each key.
I am unsure of how to upload data but below is a sample.. i am looking to create a column that provides information for the right column
collect key | accumulated |
11/9/2018TTLCTemuka CT - Clandeboye DS Grid | 1 |
11/9/2018TTLCTemuka CT - Clandeboye DS Grid | 2 |
11/9/2018TTLCTemuka CT - Clandeboye DS Grid | 3 |
11/9/2018TTLCTemuka CT - Clandeboye DS Grid | 4 |
11/9/2018TTLCTemuka CT - Clandeboye DS Grid | 5 |
11/12/2018TTLCTemuka CT - Clandeboye DS Grid | 1 |
11/12/2018TTLCTemuka CT - Clandeboye DS Grid | 2 |
11/12/2018TTLCTemuka CT - Clandeboye DS Grid | 3 |
11/12/2018TTLCTemuka CT - Clandeboye DS Grid | 4 |
11/12/2018TTLCTemuka CT - Clandeboye DS Grid | 5 |
11/13/2018TTLCTemuka CT - Clandeboye DS Grid | 1 |
11/13/2018TTLCTemuka CT - Clandeboye DS Grid | 2 |
11/13/2018TTLCTemuka CT - Clandeboye DS Grid | 3 |
10/26/2018TTLCSCS Timaru | 1 |
10/26/2018TTLCSCS Timaru | 2 |
10/26/2018TTLCSCS Timaru | 3 |
10/26/2018TTLCSCS Timaru | 4 |
10/26/2018TTLCSCS Timaru | 5 |
10/26/2018TTLCSCS Timaru | 6 |
10/26/2018KR-2HJM14SCS Timaru | 1 |
Any help is greatly appreciated.
Thanks, Shem
Solved! Go to Solution.
If you can have an extra column with a unique, consecutive numerical ID (index), I think the code below should work. You can add an index column easily in the query editor (see the solution on this post)
Table[Accumulated] = COUNTROWS ( FILTER ( Table; Table[Collect Key] = EARLIER ( Table[Collect Key] ) && Table[ID] <= EARLIER(Table[ID]) ) )
Hi @Shem,
Do the rows you want to number (with same collect key) have exactly the same values on each column (as it appears from the attached image)?
Does each row have a unique identifier (like a row number)?
If you attached a sample with the table it would probably be easier for people to help.
.
I am unsure of how to attach a sample. Hopefully, the amended post will suffice.
Thanks, Shem
If you can have an extra column with a unique, consecutive numerical ID (index), I think the code below should work. You can add an index column easily in the query editor (see the solution on this post)
Table[Accumulated] = COUNTROWS ( FILTER ( Table; Table[Collect Key] = EARLIER ( Table[Collect Key] ) && Table[ID] <= EARLIER(Table[ID]) ) )
Thanks, it worked perfectly!
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |