M Query : Create a table using input from another table

[akdoro]

I have this table, let us call it 'tblSeasons'

 StartDate   EndDate   Season

02-05-2016	5-05-2016	Round 0
11-05-2016	15-08-2016	Round 1
21-08-2016	15-11-2016	Round 2
16-11-2016	09-01-2017	Round 3
10-01-2017	15-02-2017	Round 4
01-04-2017	20-05-2017	Round 0

I want to generate another table 'SeasonDetails' with continuous dates using the above table as the input parameter :

That is:

Date	Round
02-05-2016	Round 0
03-05-2016	Round 0
04-05-2016	Round 0
05-05-2016	Round 0
11-05-2016	Round 1
12-05-2016	Round 1
13-05-2016	Round 1
14-05-2016	Round 1
15-05-2016	Round 1

etc..

How do I go about this?

[TomMartens · Accepted solution]

Hey,

here you will find a little example (open the query editor).

Basically I added a custom column using this formula

List.Dates(
  [DateStart],
  Number.From([DateEnd]) - Number.From([DateStart]) + 1,
  #duration(1,0,0,0)
)

You have to make sure that your columns are date columns and have valid entries. My Formula does not consider any Data Quality check or similar stuff 🙂 The Formula creates a list.

The next step is: expand the list as new rows:

Hope this helps

[akdoro]

@TomMartens ThankYou! That works!