none
Generate a List of Dates and Values from a Start Date and End Data RRS feed

  • Question

  • I have a data set I get each week that I load into Power BI using Power Query.  I am trying to generate a list of dates from a start date to a completion date and "spread" a value evenly across the newly generated records. Put another way:

    I want to change this:

    Code Start        End       Budget
    111   1/1/2018 1/31/18  $6200

    Into This:

    Code Date       Amount
    111   1/01/18  $200
    111   1/02/18  $200
    111   1/03/18  $200
    ...
    111  1/31/18   $200
    Total:            $6,200

    Not to complicate things, but it would be nice if I could generate only working days (M - F in this case) and divide the Budget over the working days.

    Any help would be appreciated!

    Thanks!


    John Thomas


    • Edited by jbt_PwrPvt Friday, February 16, 2018 8:00 PM
    Friday, February 16, 2018 7:49 PM

Answers

All replies

  • Hey!

    There isn't any native functions that can do that in Power Query, so you'd need to construct your own. Here's a sample on how you can accomplish that.

    Best!

    Friday, February 16, 2018 9:08 PM
    • Marked as answer by jbt_PwrPvt Wednesday, February 21, 2018 1:50 PM
    Saturday, February 17, 2018 1:46 AM
  • Miguel - did you attaché the correct file?  This doesn't seem to have anything to do with generating records between two dates with period values.

    John Thomas

    Tuesday, February 20, 2018 5:34 PM
  • Hey!

    My bad. This is the link to your file.

    Sometimes I grab the incorrect url from github for some reason

    • Marked as answer by jbt_PwrPvt Wednesday, February 21, 2018 1:50 PM
    Tuesday, February 20, 2018 6:04 PM
  • Not a problem.... gethub is blocked at work, so I'll have to look at it tonight when I get home.

    John Thomas

    Tuesday, February 20, 2018 7:56 PM
  • Herbert - thanks for your files.  The links are blocked at work so i'll have to look at them tonight.  I'll take a look a get back to you.  Thanks for taking the time.

    John Thomas

    Tuesday, February 20, 2018 7:57 PM
  • Awesome solution Miquel.  Thanks for taking the time. 

    John Thomas

    Wednesday, February 21, 2018 1:54 PM
  • Awesome solution Herbert.  You went the extra mile on this one.  Thanks for taking the time.  There are several functions in this solution I'll have to spend some time to fully understand. For example, "List.Accumulate"!  This solution is part of an overall effort to turn a schedule report into a proper data set for analysis over a 5 year project and about 60,000 activities in the schedule, all of which have a start and end date.  I'll be grouping the activities by areas and disciplines (our lowest level of reporting for this purpose) so the list isn't as large.  Lots of the code you have here will be helpful in arriving at a solution.

    Thanks again...


    John Thomas


    • Edited by jbt_PwrPvt Wednesday, February 21, 2018 2:01 PM
    Wednesday, February 21, 2018 2:00 PM