Date Query

[efilipe]

Hi Guys,

I have used a video explaining how to make a query to have a date table. Worked pretty well, until I found out my client uses dates in the future. I need to change the code to include all dates from 2017. 01/01/2017 to 31/12/2017. Any idea?  Thank you!

This is the code:

let
Source = List.Dates,
#"Invoke dates" = Source(#date(2017, 04, 01), Duration.Days(DateTime.Date(DateTime.FixedLocalNow())- #date(2017, 04, 01))+1, #duration(1, 0, 0, 0)),
#"List to table" = Table.FromList(#"Invoke dates", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Date = Table.RenameColumns(#"List to table",{{"Column1", "Date"}}),
#"Day Added" = Table.AddColumn(Date, "Day", each Text.PadStart(Number.ToText(Date.Day([Date])),2,"0")),
#"Day Name Added" = Table.AddColumn(#"Day Added", "Day Name", each Date.ToText([Date],"ddd","pt-BR")),
#"Month No Added" = Table.AddColumn(#"Day Name Added", "Month No", each Date.ToText([Date],"MM","pt-BR")),
#"Month Name Added" = Table.AddColumn(#"Month No Added", "Month Name", each Date.ToText([Date],"MMM","pt-BR")),
#"Quarter No Added" = Table.AddColumn(#"Month Name Added", "Quarter No", each Date.QuarterOfYear([Date])),
#"Week No Added" = Table.AddColumn(#"Quarter No Added", "Week No", each Text.PadStart(Number.ToText(Date.WeekOfYear([Date])-1),2,"0")),
#"Year Added" = Table.AddColumn(#"Week No Added", "Year", each Date.Year([Date])),
#"Year Month Added" = Table.AddColumn(#"Year Added", "Year-Month", each Number.ToText([Year])&"-"&[Month Name]),
#"Year Quarter Added" = Table.AddColumn(#"Year Month Added", "Year-Quarter", each Number.ToText([Year]) & "Q"& Number.ToText([Quarter No],"00")),
#"Change type to text" = Table.TransformColumnTypes(#"Year Quarter Added",{{"Year", type text}, {"Date", type date}, {"Month No", type text}, {"Day", type text}, {"Day Name", type text}, {"Month Name", type text}, {"Quarter No", type text}, {"Year-Quarter", type text}, {"Year-Month", type text}}),
#"Sort Day" = Table.AddColumn(#"Change type to text", "SortDay", each Date.Day([Date])),
#"Sort DayName" = Table.AddColumn(#"Sort Day", "SortDayName", each Date.DayOfWeek([Date],1)),
SortWeekNo = Table.AddColumn(#"Sort DayName", "SortWeekNo", each Date.WeekOfYear([Date])-1),
#"Sort YearMonth" = Table.AddColumn(SortWeekNo, "SortYearMonth", each [Year]&Text.PadStart([Month No],2,"0")),
#"Sort YearQuarter" = Table.AddColumn(#"Sort YearMonth", "SortYearQuarter", each [Year]&Text.PadStart([Quarter No],2,"0")),
#"Changed Type to NO" = Table.TransformColumnTypes(#"Sort YearQuarter",{{"SortYearMonth", Int64.Type}, {"SortYearQuarter", Int64.Type}, {"SortDayName", Int64.Type}, {"SortDay", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type to NO",{{"Date", "Data"}, {"Day", "Dia"}, {"Day Name", "Dia nome"}, {"Month No", "Mês número"}, {"Month Name", "Mês nome"}, {"Year", "Ano"}, {"Year-Month", "Ano-mês"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Quarter No", "Week No", "Year-Quarter", "SortDay", "SortDayName", "SortWeekNo", "SortYearMonth", "SortYearQuarter"}),
in

[MarcelBeug · Accepted solution]

Remark: your code is incomplete as there is nothing after "in" and a comma is preceding "in".
It doesn't matter though as the adjustment will be in the first part of the code.

One way, closest to your solution, adjust the second step to:

#"Invoke dates" = Source(#date(2017, 1, 1), Duration.Days(#date(2017,12,31)- #date(2017, 1, 1))+1, #duration(1, 0, 0, 0)),

Another way (my preference): fist create a list with numbers representing dates, after conversion to table, adjust the data type to date.

let
    Source = {Number.From(#date(2017,1,1))..Number.From(#date(2017,12,31))},
    #"List to table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"List to table",{{"Column1", type date}}),
    Date = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),

No further changes to the code.

[efilipe]

Thanks Marcel! Worked! I'll try the other way when I learn a little more!! 🙂 Thanks!!