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.
Hey guys, I am trying to get data from a rostering system. I am not sure if this is just a bad design or if that is common set up but the full day roster is broken down into 15 minute intervals starting at midnight and this is all stored in one field in the below format.
32,20C2L16C
The "," marks no activity so 32/4 means that this person started at 8am, then worked for 5 hours before taking 30 minute break for lunch (L) and then doing another 4 hours of work.
I was thinking that one way to solve it would be to do repeat on all the non-numeric characters to get something like this:
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCLLCCCCCCCCCCCCCCCCC
And then to simply count all Cs and divide by 4. Is it possible to do the repeat like this with DAX (or in PQ)? Would there be a better way that I may be missing?
Hi Greg,
yeah it is a bit confusing but you are on it. The non-numeric characters mark 'activity' and the preceding number marks the amount in 15 minute intervals.
, = no activity
L = lunch
C = work
32, = 32x 15 minute intervals of no activity since midnight = 8 hours of no activity, this is kind of irrevelant here, it just means that the next activity started at 8 am
20C = 20x 15 minute intervals of work = 5 hours of work
2L = 2x 15 minute intervals of lunch break = 30 minute break
16C = 16x 15 minute intervals of work = 4 hours of work
I am trying to find out how to use the rept for each of the non-numeric characters to get to this:
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,CCCCCCCCCCCCCCCCCCCCLLCCCCCCCCCCCCCCCC
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 | |
100 | |
86 | |
64 |