cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
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
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.

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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

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

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors