Create a table with dates from a table with only a startdate and enddate

[rolf1994]

Hi,

I have the following table:

ID	StartDate	EndDate
T123	3-8-2018	7-8-2018

I want to create the following table based on my existing table:

ID	Date
T123	3-8-2018
T123	4-8-2018
T123	5-8-2018
T123	6-8-2018
T123	7-8-2018

How to do this?

[v-lili6-msft]

hi, @rolf1994

If Date from StartDate to EndDate are only the same day of each month?

If so, you need a date table and use CROSSJOIN Function to create a table

Table = FILTER(CROSSJOIN(Table1,'Date'),Table1[StartDate]<='Date'[Date]&&'Date'[Date]<=Table1[EndDate]&&DAY('Date'[Date])=DAY(Table1[StartDate]))

Result:

Best Regards,

Lin

[DaFloDo]

hi @rolf1994,

Following Power Query M did the job:

let
    rec = SourceTable{0},
    id = rec[ID],
    start = rec[StartDate],
    end = rec[EndDate],
    dayCount = Duration.Days(end - start) + 1, 
    dateList = List.Dates(start, dayCount, #duration(1,0,0,0)),
    toTable = Table.FromList(dateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    RenamedColumn = Table.RenameColumns(toTable,{{"Column1", "Date"}}),
    changedType = Table.TransformColumnTypes(RenamedColumn,{{"Date", type date}}),
    addedId = Table.AddColumn(changedType, "ID", each id, type text),
    reorderedColumns = Table.ReorderColumns(addedId,{"ID", "Date"})
in
    reorderedColumns

best regards

Florian

[petrovnikitamai]

i did it in pq

1. create dateList in blank query

=List.Dates(#date(2018,01,01);300;#duration(1,0,0,0))

2. create function where start_date and end_date is parametrs

let
getDateList=(start_date as date, end_date as date)=>
let
    Source = Table.FromList(List.LastN(List.FirstN(dateList, each _ <end_date), each _ >=start_date), Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    Source
in
    getDateList

3. invoke function in your table

[PattemManohar]

@rolf1994 Please try this using "New Table" option

GenerateDatesOut = SELECTCOLUMNS(CROSSJOIN(CALENDAR(MIN(GenerateDates[StartDate]),MIN(GenerateDates[EndDate])),GenerateDates),"Date",[Date],"ID",[ID])