Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Generating Date and Random times within parameters

Hi Guys,

 

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

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

 

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

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous 

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

     

    Best Regards!

    Yolo Zhu

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      So far I have something like that.

      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

      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.

       

      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