Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
User | Count |
---|---|
78 | |
75 | |
62 | |
60 | |
47 |
User | Count |
---|---|
109 | |
95 | |
86 | |
79 | |
61 |