Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
SeekK
New Member

Generating Date and Random times within parameters

Hi Guys,

 

I have a request. I have a table like that in power query.

SeekK_0-1709041165903.png

For each 16€ in column "Rac vred" I would like to generate new line. So in Column "costum" is a number of how many new lines need to be generated. Also the catch is that on the same date no more than 4 occurences can happen. So if there are more power query should write date one day earlier in 5th row. I have kind of managed to achieve that with this

 

List.Dates([Dat storitve]+#duration(0,23,0,0),[Custom],#duration(0,-6,0,0))

 

and with expanding rows I got this (costum.8) column

SeekK_1-1709041511946.png

 

It could be better so that the intervals of how many occurences can be in a day would be random but never more than 4. But I do not know how to write this 🙂

 

This is the first part.

After dates are generated. I would then like to generate time for each row. 

Conditions would be

  • between 8 am and 5 pm
  • full hour or half an hour
  • for each day in column Costum.8 there should be at least 2 hours between generated time (so for example that if for January 1st. I have 4 rows generated times should not overlap.

Is this even possible?

 

Thanks in advance

 

2 REPLIES 2
v-xinruzhu-msft
Community Support
Community Support

Hi @SeekK 

Can you provide some sample output you want so that can privodei more suggestion for you.

 

Best Regards!

Yolo Zhu

So far I have something like that.

SeekK_1-1709109715103.png

So for every 16€ in column "I" a new row is generated. And if there is more than 4 rows for each date in column M, every fifth row generated includes one day eariler. 

 

I have achieved that with this 

List.Dates([Dat storitve]+#duration(0,23,0,0),[Custom],#duration(0,-6,0,0))

If you have a bteer and more reliable way I would be happy to fix that

But even this is not optimal. Since as you can see for 6/29/2023 there are two incovies (one for 240 and other for 320) so there is more than 4 occurences for 6/28/2023 and 6/27/2023.

Ideally this would look like this

SeekK_2-1709110318520.png

In yellow manual corrections. So no more than 4 occurences on any given day even if 2 invoces overlap.

Is this too complicated?

 

Once that would be achived then for each row I would require in column date to be generated between 8.am and 6.pm. But hoursthat refer to the same day have in column.8 need to be at least two hours apart.

SeekK_4-1709111166135.png

 

Does this make sense at all?

 

Background behind this is that incovies instead of every day are issued lets say once a month per person. So travel orders are to be automatically and randomly generated for each invoice but need to be realistic so that I do not have two travel orders overalping on the same day that is the main point.

 

Thank you in advance and best regards

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors