Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Data in difficult format

Hi,

 

Has anybody any experience in working with data in a similar format?

 

The data shows an employees (carerid) availability in minutes.

 

If we look at the first 1st record - 

 

carerid = 30199 is available Mon, Tues, Wed (Column 'day', values 123) for 1440 minutes (Column 'length', value 1440) starting 22/07/2019 and ending 09/10/2022. (Column 'week', value 2) means this availability recurs every second week)

 

What I am trying to achieve is from a Calendar table filter, select any date range that will give each employees availability minutes (Column length).

 

Any advice appreciated.

 

 

 

Need Help

2 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    3 years ago

    Hi, Anonymous 

    Can you give an example to further explain your needs?

    Best Regards,
    Community Support Team _ Eason

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      v-easonf-msft 

      I would like to generate a list of dates in Power Query.

      If for e.g a record has a start date of 22/07/2019 and the week col is 2, the next date for this record should be 05/08/2019 then 19/08/2019 and so on until the enddate is reached. Each date should be 14 days from the previous date (if week value = 2).

       

      If week value = 0 then there is no recurring date. If it is 3 then each record should add a new date 21 days from the start date and so on again until the enddate is reached.