Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to create a table from a range of dates?

Hello

 

Can you tell me please how to create a table from a list of dates?

 

The range is Today till 5 days ago.

 

So far I have done:

 

let
StartDate = Number.From(DateTime.Date(DateTime.LocalNow())),
EndDate = Number.From(Date.AddDays(DateTime.Date(DateTime.LocalNow()),-5)),
Source = List.Range({StartDate..EndDate},0,1)
in
Source

 

But it outputs just the word 'List' !

 

Any idea?

Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    Anonymous 

    Try this please:

    let
StartDate = Number.From(DateTime.Date(DateTime.LocalNow())),
EndDate = Number.From(Date.AddDays(DateTime.Date(DateTime.LocalNow()),-5)),
Source = {EndDate..StartDate},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}})
in
#"Changed Type"

     



2 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    Anonymous 

    Try this please:

    let
StartDate = Number.From(DateTime.Date(DateTime.LocalNow())),
EndDate = Number.From(Date.AddDays(DateTime.Date(DateTime.LocalNow()),-5)),
Source = {EndDate..StartDate},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}})
in
#"Changed Type"

     



  • ronrsnfld's avatar
    ronrsnfld
    Icon for Super User rankSuper User
    5 years ago

    You may need to alter the number of days argument in Date.AddDays and the Count argument in List.Dates depending on exactly what you mean by "Today to till 5 days ago"

    Use the List.Dates function:

     

     

    let
    StartDate = DateTime.Date(Date.AddDays(DateTime.LocalNow(),-5)),
    listDates = List.Dates(StartDate,6,#duration(1,0,0,0)),
    Source = Table.FromColumns({listDates}, type table [Dates=Date.Type])
in
    Source