cancel
Showing results for
Did you mean:
Helper I

## How to do character repeat in PBI

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?

2 REPLIES 2
Super User IV
Well, first I don't see how your explanation matches the data posted you mention 32/4 but I don't see that in the data you posted, I see 32,20 so I can't make heads or tails of any of that or are you saying that there are 32 commas and then 20C's or ??

But regardless, in DAX REPT is the function to repeat characters.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Helper I

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

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!